Query Syntax Specification
Gigya queries use the same syntax rules as SQL, but not all standard SQL keywords are available.
- Deleted objects do not appear in queries.
- 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.
- Example queries and responses can be viewed at DS.search Examples.
select - The "select" statement accepts a comma separated list of fields or objects to retrieve. Acceptable values for this statement are:
- Field names, specifying the complete path, i.e. data.album.photo.photoTitle_t. Specifying partial fields names (data.album) will return all the fields in the path.
- Object names, specifying an object name, i.e. user_likes will return all the fields in the user_likes object.
- Partial field names (fields that contain only a part of the path to sub-objects, i.e. data.album) - will retrieve everything below that path.
- * - will retrieve every field in the schema.
- count(*) - Returns the number of objects for a particular object type in the data store. The result is given in the response as a single value inside the "data" field.
as - create an alias (temporary title) for the returned object or field. 'Select track.title as song...' will return track.song containing the values of track.title.
- sum(), min(), max(), avg(), sum_of_squares(), variance(), std() - Mathematical functions, must all be performed on the same numeric field. Fields with null values will be ignored.
The name of the field on which the function is to be performed must be entered in the brackets. For example: 'Select min(track) from music'.
- sum - provides a total for the field in the brackets.
- min/max - minimum/maximum value for the field. If no values are found, min will return "infinity" and max will return "-infinity".
- avg - average value of the field.
- variance - the extent that the field's values vary.
- std - standard deviation, the likelihood that values vary.
from - Name of the data source. Only one data source is supported. Queries on objects in the Data Store (DS.search) must specify the object "type" classification as defined in the DS.store method.
where - The "where" clause defines conditions for selecting items from the collection. Supported operators:
- >, >=, <, <=, =, != - the left operand must be a data field name (with a proper suffix letter) and the right operand must be a constant of the same type as the field. For example: "where track.# >= 18 ".
- Only = and !=can be used with encrypted fields.
- The = operand is case-insensitive.
- and, or
- contains, not contains (string operators) - may be used only on fields of type "text" and "string" (see details on the different field data types).
- contains is not case-sensitive.
- The left operand must be a text field name and the right operand must be a constant string. You can search for a specific word within the string, for example: 'where data.about_t contains "music" '. Underscores are treated as separators between words. Note that you can search for words that are part of a sentence, but you cannot search for part of a word.
- Contains cannot be used to locate words within encrypted fields (you must enter the full string) but is case insensitive.
- contains, not contains (array operators) - may be used on arrays.
- The left operand must be an array field name and the right operand must be a constant of the same type as the array values.
- The array field name must have a suffix denoting the type of the arrays values.
- Example: 'where data.hobbies_s contains "swimming" '.
- in() - only retrieve items if the field contains one of the list values. For example: 'select * from music where track.album_artist in ("Elvis", "Beatles", "Santana")' will return tracks recorded by the specified artists.
- is null, is not null
- regex('') - 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.
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 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 not show up in the query results.
The count(*) function returns the number of records in a table. This allows you to count objects without actually fetching them from the DS.
According to the response, the number of records in table_name is 298.
For text fields that have been defined with multiple language compatibilities, you can search the field in a specific language by using lang(,) instead of just the field name. For example, if the field "profile.nickname" has been defined with the added language Japanese, you can use:
- where profile.nickname = "rotem" - to search across all languages
- where lang(profile.nickname, ja) = "rotem" - to search the field in Japanese
- where lang(profile.nickname, default) = "rotem" - to search the field in the default language
In addition, lang can be combined with all string operators, such as CONTAINS: for example, where lang(profile.nickname, ja) CONTAINS "rotem".