Query Guide

Queries are handy when you want to extract a dataset from Rhythm, which might include data from multiple apps. Generally, this data is used to synchronize another system or to establish a cache that you manage. You could then access this data behind your own APIs. These kinds of queries are usually executed on a regular interval, such as every hour, and may define criteria to only return records that have been modified in that time.

Authentication

Since queries are not recommended for direct user interactions, they are usually executed by a script or other scheduled process. In these machine-to-machine scenarios, the client credentials authentication flow is required to generate your access token.

Please review the authentication quick start for more information and be sure to cache those tokens if you are going to execute more than one query per-day.

Overview

Start by deciding where to store your query. You can store the query as an asset in your program (static query), or retrieve it at execution time from the Rhythm API (dynamic query). Below we'll take a look at how those flows differ.

Starting a Query

Once you have your query, you can POST it to the System API > Queries endpoint. In the URI of this POST, you can specify the query type, and the format for the results. Valid values include json, csv, excel, excelUnformatted, or xml. This POST will start your query and return you the query execution ID.

Getting Query Results

You can use the query execution ID in the URI of the System API > Queries endpoint. The response will return information about the execution including its current status. Once the status is FINISHED, the response will also include a URL where you can retrieve the query results or metadata about the fields included in the results.

If the query status is not yet FINISHED, you can delay for a second or two, then retry the request to get the updated query status. Since this polling mechanism is common for long-running tasks on a REST API, there are many libraries available in major frameworks that will handle this for you. For JavaScript, we recommend using https://www.npmjs.com/package/async-retry with the following default configuration:

{
  retries: 10,
  factor: 1.25,
  minTimeout: 1000,
  maxTimeout: 3000,
}

Static Query

Storing your query definition as an asset in your program is useful when your program requires a particular result set. This puts you in full control of your query definition, which you can even version control.

Dynamic Query

Retrieving the query at execution time is useful when you want to allow Rhythm console users to easily change the definition of the query in the future.

The dynamic query flow is similar to the static query, however before the query is started, it is retrieved from the Platform API > Saved Queries endpoints. The query may be manipulated before starting it to ensure required fields or criteria are included.

Criteria

Whether you are storing the query definition in your source code or retrieving it at runtime, it is often helpful to override the criteria before executing your query. You might do this to only retrieve records that have been modified since the last time the query was executed.

Every query has an optional criteria property which uses JSON Rules Engine syntax. Because this syntax is recursive, the criteria property only appears with the type object in the Reference: System documentation and samples.

If you are having trouble defining your criteria, we recommend using the Rhythm UI to build a query definition. You can use your browser's inspector to see how different options change the query definition. To get access to the Rhythm console, please contact support@rhythmsoftware.com

Alternatively, you can save your query, then retrieve it from the API using the Platform API > Saved Queries endpoints.

Grouping

The criteria object begins with a grouping. This can be an any group (OR) or an all group (AND). Both groups are arrays which contain the individual criteria objects to examine for this group. Every criteria object in this group can optionally define their own any or all arrays to support nested grouping.

Fact

Every criteria must specify a fact which refers to the table that contains the data to compare. fact values must correspond to a table_alias value from the query type being executed.

Operator

Every criteria must specify a operator to use in comparison. operator can be one of: equal, notEqual, containsString, regex, lessThan, lessThanInclusive, greaterThan, greaterThanInclusive, in, notIn, contains, doesNotContain, isBlank, isNotBlank

Path

Every criteria must specify a path identifying the field to compare. This value uses JSON path to locate the path. This might be a value like $.first_name.

Value

Every criteria must specify a value. How this value is compared to the field defined in fact and path is determined by the operator you select.

Examples

Criteria

You can combine multiple criteria such this example criteria object:

{
  "all": [
    {
      "any": [
        {
          "path": "$.first_name",
          "fact": "main",
          "value": "Tony",
          "operator": "notEqual"
        },
        {
          "path": "$.last_name",
          "fact": "main",
          "value": "Stark",
          "operator": "equal"
        }
      ]
    },
    {
      "path": "$.rolodex_contacts__company_name__c",
      "fact": "main",
      "value": false,
      "operator": "isNotBlank"
    }  
  ]
}

Query

The following is an example query definition for the rolodex:contacts query type. This JSON can be sent as the body of a POST to https://system.api.rhythmsoftware.com/queries/{tenantId}/start/rolodex%3Acontacts/json to start a new query:

{
  "output_fields": [
    {
      "name": "name"
    },
    {
      "name": "organization_name"
    },
    {
      "name": "email_address"
    },
    {
      "name": "preferred_address__city"
    },
    {
      "name": "preferred_address__state"
    },
    {
      "name": "rolodex_contacts__company_name__c",
      "label": "Company Name"
    },
    {
      "name": "rolodex_contacts__companyname__c",
      "label": "companyName"
    }
  ],
  "name": "Contacts with Organization and Company Name",
  "sort_fields": [
    {
      "name": "name"
    }
  ],
  "criteria": {
    "all": [
      {
        "path": "$.rolodex_contacts__company_name__c",
        "fact": "main",
        "value": false,
        "operator": "isNotBlank"
      }
    ]
  }
}