Query Syntax Specification
Gigya queries use the same syntax rules as SQL, however not all standard SQL keywords are available.
When querying for text/strings, values must be wrapped in quotes. e.g.,
- Unsupported SQL syntax in the query string (e.g., HAVING) will produce an error.
- The query string clauses must be ordered in the following way:
- Encrypted fields are decrypted during searches but comparison operators (>, >=, <, <=) and regex expressions are not available on these fields. The Contains keyword can be used for case-insensitive searches on encrypted fields but does not support partial strings.
SELECT - The "SELECT" statement may only be used to select all fields ( * ).
FROM - Name of the data source. Only one data source is supported. Audit queries must state "FROM auditLog" (audit.search).
WHERE - The "WHERE" clause defines conditions for selecting items from the collection. Supported operators:
- > , >=, <, <= , = , != - Only = and != can be used with encrypted fields. The "=" operand is case sensitive.
- AND , OR
- in() - only retrieve items if the field contains one of the list values. For example: 'SELECT * FROM auditLog WHERE errCode in ("0", "400006", "400009")'.
- is null , is not null
- regex ('<regex-pattern>') - defines a search term using regex formatting. The regex syntax can be found in: Regular Expression Language Reference. Regex patterns cannot be used on encrypted fields.
ORDER BY - The "order by" clause specifies a list of fields by which to sort the result objects.
LIMIT - Using the "LIMIT" clause, you may specify the maximum number of returned result objects. If not specified, the default is 300. The maximum limit value accepted is 10000. If the search is sent with openCursor = true, LIMIT will set the batch size. LIMIT must be the last item in the query. Example:
Please note, when using a cursor, the number of results in a batch is not guaranteed.
START - The "START" clause (not an SQL standard clause) may be used for paging. The clause specifies the start index from which to return result objects. Start can not be used with openCursor.
The 'SELECT - FROM - WHERE - ORDER BY' query creates an (internal) indexed list of objects. By also using the START & LIMIT clauses, you will receive a subset of this list, starting with the start index and ending with start+limit index.
Note: When implementing paging, there is no guarantee that there will be no duplications or that recently added data will show up in the query results.