Gigya Job Openings

Page History

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Field names, specifying the complete path, i.e. data.album.photo.photoTitle_t, profile.firstName. Specifying partial fields names (data.album) will return all the fields in the path.
  • Object names, specifying an object type, i.e., profile will return all the fields in the Profile 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(*)  - if the data source is accounts, returns the number of accounts. If the data source is an object type, returns the number of objects 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 profile.firstName AS contactName...' will return a field inside the profile obj called contactName containing the values of profile.firstName. Example:

    Code Block
    // Query:
    SELECT profile.firstName AS contactName FROM accounts
     
    // Returns
    {
      "results": [
        {
          "profile": {
            "contactName": "Eric"
          }
        },
        {
          "profile": {
            "contactName": "Igor"
          }
        },
        {
          "profile": {
            "contactName": "Limor"
          }
        },
            ... snipped ...
      ],
      "objectsCount": 300,
      "totalCount": 1032,
      "statusCode": 200,
      "errorCode": 0,
      "statusReason": "OK",
      "callId": "ad24d124f11149729acdb0e7c6a6e590",
      "time": "2017-05-04T09:14:35.008Z"
    }
  • 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(profile.age) FROM accounts'.
    • 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. Account and IDS queries must state "FROM accounts"  (accounts.search or IDS.search). Data will be retrieved from the Profile object and/or the user defined data object in the user accounts.

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 profile.age >= 18 ". 
    • Only = and != can be used with encrypted fields.
      *Note: The "=" operand is case sensitive.
  • and or
  • contains, not contains  - may be used only on text (string) fields and arrays. 
    • Text (string) fields - supports standard full text search capabilities. Contains is not case sensitive, except when used on encrypted fields. 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.
      If you want to perform a search on an encrypted field, you must enter the full string. Encrypted fields include the fields you encrypted and also fields that Gigya encrypts
    • 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 accounts WHERE profile.firstName IN ("Frank", "Dean", "Sammy")' will return users with the specified first names. 
  • is null is not null
  • not
  • 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. You can not use ORDER BY on encrypted fields.

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. 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. The maximum start value accepted is 5000.

The 'select - from - where - order by' query creates an (internal) indexed list of objects. By using the start and limit clauses, you will receive a subset of this list, starting with the start index and ending with start+limit index.

Notes:

  • When implementing paging, there is no guarantee against duplications or that recently added data will show up in the query results.
  • start cannot be used with openCursor.

Counters - Counter data is only available if the data source statement ("from") specifies it, i.e., "FROM accounts WITH counters". For example: ' SELECT * FROM accounts WITH counters LIMIT 5'. To view only the counter fields, 'SELECT counters FROM accounts WITH counters'. To specify a particular field in the counters object, use "counters.class" or "counters.path" etc. Note that counter data is not retrievable using the accounts.search API until 1-24 hours after it is written. To get real-time counter data, use accounts.getCounters.

Two SQL keywords exist for use with counter fields (they are not available with other fields): ifElement is used for setting multiple conditions on a single counter's different fields and filter counters is used to restrict the counters returned with an account object.

  • filter counters by class="<class name>" - restricts the counter types returned with the account object to those listed in the filter statement. For example: filter counters by class="shares", returns only the shares counters.
  • ifElement(counters, <if statement relating to different elements of a single counter>) - this function allows you to apply conditions to several elements within the same counter, for example: ifElement(counters, class="purchases" and period = "total" and value > 100).  When true the ifElement expression returns 1.
Include Page
Template_gs GCS Query Syntax internal
Template_gs GCS Query Syntax internal