Gigya Job Openings

Template_gs GCS Query Syntax Accounts

Skip to end of metadata
Go to start of metadata

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 accounts WHERE name = "John Doe".
  • When querying for Integer (and other non-textual fields) values, value must not be wrapped in quotes. e.g., SELECT * FROM accounts 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*:
    1. Select clause
    2. From clause
    3. Where clause
    4. Filter clause
    5. Group By clause
    6. Order By clause
    7. Start clause Or/And Limit clause

      1. *Queries ordered differently will produce an error.
          For example:
        1. SELECT *, profile.age FROM accounts GROUP BY profile.age LIMIT 5  - is a valid query 
          SELECT *, profile.age FROM accounts LIMIT 5 GROUP BY profile.age - will produce an error

  • 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. Usernames, emails, friends' names and friends' emails are encrypted by default, additional fields may be set as encrypted by the site
  • Deleted accounts do not appear in queries.
  • Query examples can be generated and query commands tested using the Identity Query Tool in Gigya's website: After signing in, go to Reports >> User Identities >> Identity Query Tool or click here.

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, 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:

    // 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 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.
Unable to render {include} The included page could not be found.