Searches and retrieves data from Gigya's Data Store (DS) using an SQL-like query. For security reasons this method is not available for client side SDKs, only for server side SDKs. SQL queries are converted into Gigya's proprietary query language. SQL injection attacks are not possible because queries are both created by the customer and then converted by Gigya.
A short delay is possible between the writing of account data to the data base and its availability in queries.
Query Syntax Specification
Gigya queries use the same syntax rules as SQL, however not all standard SQL key words are available.
- When querying for string values, value must be wrapped in double quotes. e.g., SELECT * FROM music WHERE guitar = "Fender".
- When querying for Integer (and other non-textual fields) values, value must not be wrapped in quotes. e.g., SELECT * FROM music WHERE age = 42.
- 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*:
- Select clause
- From clause
- Where clause
- Filter clause
- Group By clause
- Order By clause
- Start clause Or/And Limit clause
- 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 a field called 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.
- and , or
- contains, not contains - may be used only on text (string) fields and arrays.
- Text (string) fields - support standard full text search capabilities. 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. Contains cannot be used to locate words within encrypted fields (you must enter the full string) but is case insensitive.
- 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. For example: 'where data.hobbies_s contains "swimming" '.
Note: You can only search words that are part of a sentence, you cannot search for parts of a word.
- 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.
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 show up in the query results.
Below are a few points to note regarding query optimization:
- Query execution is based on clause position and is executed from left to right.
Place clauses that have the greatest impact on records returned at the beginning of your SQL statement. For example, to retrieve a list of male users over the age of 25:
This is because filtering first by gender automatically reduces the result set by half, so the server only needs to run the next filter on half of the overall population.
- A NOT clause (NOT or !) is executed on a single statement immediately to it's right, after analyzing the statement. A single statement can hold several conditions inside parentheses.
- Date ranges are calculated much more efficiently using a timestamp field rather than a date field.
- Use of regex is computationally intensive and can significantly increase response time.
- AND clauses take precedence over OR clauses (i.e., AND clauses are executed before OR clauses).
- Use parentheses to modify default precedence (e.g., to execute an OR operation before an AND operation).
When running long queries (>5,000 records returned), it's best practice to paginate your results using cursors. If you do not use cursors, results are limited to a total of 5,000 records per query (not just per page).
To use cursors, during the first request, pass query=<query to execute> and openCursor=true. The response will include the nextCursorId field, containing a cursor ID to be used in the next request. On subsequent requests, pass cursorId=<last response's nextCursorId> and do not submit the query again. The absence of the nextCursorId field in a response indicates the end of the result set.
When using openCursor, you cannot use 'START'.
us1- For the US data center.
eu1- For the European data center.
au1- For the Australian data center.
ru1- For the Russian data center.
If you are not sure of your site's data center, see Finding Your Site's Data Center.
|||query||string||A SQL-like query specifying the data to retrieve. Please refer to the Query language specification section above.|
An HMAC_SHA1 signature proving that the search call is in fact coming from your client application, in order to prevent fraud. Follow the instructions in Constructing a Signature using the following base-string: query + "_" + expTime . When using cursors, this parameter should only be sent with the initial request and omitted from subsequent requests.
This parameter is required only when calling the search method from client side (i.e., Mobile SDKs)
The GMT time when the querySig parameter should expire. The expected format is the Unix time format (i.e., the number of seconds since Jan. 1st 1970). Gigya checks the time when the search request is received. If the time succeeds expTime, the request is considered forged.
This parameter is required only when calling the search method from client side (i.e., Mobile SDKs)
||openCursor||Boolean||When set to true, the search response will include, in addition to the first page, another field named nextCursorId, which is used to fetch the next batch of results. This parameter should only be used on the first request and later should be removed from the request. When openCursor is true, the Limit clause sets the number of results returned in the batch.|
Note: You cannot use a cursor if you have a 'group by' or when using 'start'.
|||cursorId||string||The cursor ID that contains the nextCursorId value received in the first search call. Note: You cannot pass both cursorId and query on the same request - cursorId brings the next page for the search for which it was opened. Also, the time between search requests using a cursorId must not exceed 5 minutes.|
Note: Each request should contain a different cursorId obtained from the response of the previous request (not the first) using the nextCursorId field. The exception to this rule is when a request fails or when a particular result set needs to be resent; in this case, resend the same cursorID (as long as it has not expired) to receive its associated result set.
|||timeout||integer||The timeout for the request (in milliseconds). Default value is 20000 (20 seconds). Maximum allowed value is 60000 (60 seconds).|
Each REST API request must contain identification and authorization parameters.
Please refer to the Authorization Parameters section for details.
|data||Array||An array of data objects retrieved from the DS.|
|objectsCount||integer||The number of objects returned in the "data" array.|
|totalCount||integer||The total number of object that satisfy the query in the DB. This is useful for knowing how many objects are in the DB, when fetching limited amount using the "limit" parameter.|
|nextCursorId||string||Used to fetch the next batch of results. This parameter is not returned on the last batch of results, its absence means that the result set is finished.|