Query language

Overview #

The query language enables you to search for records within an entity using complex criteria. This language is used by both the REST API and the Javascript API. However, please note that the method of specifying queries differs between the two.

For example, a simple query to retrieve 10 active companies using the REST API looks like this:

GET https://app.{{ site.slingr_domain }}/prod/runtime/api/data/companies?state=active&_size=10

In contrast, with the Javascript API, it would look something like this:

var records = sys.data.find('companies', {state: 'active', _size: 10});
while (records.hasNext()) {
  // do something
}

In the Javascript API, you can also utilize the query builder), which provides helpful tools for constructing queries:

var query = sys.data.createQuery('companies')
    .field('state').equals('active')
    .size(10);
var records = sys.data.find(query);
while (records.hasNext()) {
  // do something
}

Generally, when dealing with more intricate query criteria, the query builder is easier to use than the simple map form because it offers conversion and parsing features. Additionally, there are queries that cannot be expressed using the query map version. For instance:

var query = sys.data.createQuery('companies').query(function(q) {
  return q.or(
    q.field('name').like('test'),
    q.field('name').equals('abc')
  );
});

The above query cannot be expressed using the query map version because you cannot use the same key in the map. It would look like this:

var query = {
  name: 'like(test)',
  name: 'like(abc)'
};

In this case, the key name appears twice in the map and won’t work as expected.

Another consideration is exception handling. When using the query builder, exceptions may be thrown during query construction. For example:

try {
  var query = sys.data.createQuery('companies')
      .field('doesNotExist').equals('test');
  log('count: '+sys.data.count(query);
} catch (e) {
  log('error: '+e.message());
}

The script above will throw an exception when calling the field() method because the field doesNotExist is not a valid field in the companies entity. Similar exceptions will occur with other improper query configurations, so be aware that exceptions may arise during query construction.

On the other hand, when using the query map, exceptions won’t occur during query construction but rather when executing the query:

try {
  var query = {doesNotExist: 'test'};
  var count = sys.data.count('companies', query);
  log('count: '+count);
} catch (e) {
  log('error: '+e.message());
}

In this case, the exception will be thrown when calling the sys.data.count() method.

This documentation section provides samples in all three versions for comparison. If a feature only applies to specific versions, it will be explicitly indicated.

Simple queries #

Simple queries allow you to filter records using the AND logical operator exclusively, although you can still specify multiple filters. Here are some simple query examples:

// finds a company by exact name 
var records_00 = sys.data.find('companies', {name: 'Blogpad'});
log('total: '+records_00.count());
while (records_00.hasNext()) {
    log(records_00.next().label());
}

// finds a company by exact name 
var query_00 = sys.data.createQuery('companies')
    .field('name').equals('Blogpad')
var records_00 = sys.data.find(query_00);
log('total: '+records_00.count());
while (records_00.hasNext()) {
    log(records_00.next().label());
}

// finds a company by exact name 
GET /data/contacts?name=Blogpad

// filters by companies with more than 200 employees; limits results to 10 and use offset of 20 
var records_01 = sys.data.find('companies', {numberOfEmployees: 'greater(200)', _size: 10, _offset: 20});
log('total: '+records_01.count());
while (records_01.hasNext()) {
    log(records_01.next().label());
}

// filters by companies with more than 200 employees; limits results to 10 and use offset of 20 
var query_01 = sys.data.createQuery('companies')
    .field('numberOfEmployees').greater(200).size(10).offset(20)
var records_01 = sys.data.find(query_01);
log('total: '+records_01.count());
while (records_01.hasNext()) {
    log(records_01.next().label());
}

// filters by companies with more than 200 employees; limits results to 10 and use offset of 20 
GET /data/contacts?numberOfEmployees=greater(200)&_size=10&_offset=20

// filters by two fields 
var records_02 = sys.data.find('companies', {type: 'a', isCustomer: false});
log('total: '+records_02.count());
while (records_02.hasNext()) {
    log(records_02.next().label());
}

// filters by two fields 
var query_02 = sys.data.createQuery('companies')
    .field('type').equals('a').field('isCustomer').equals(false)
var records_02 = sys.data.find(query_02);
log('total: '+records_02.count());
while (records_02.hasNext()) {
    log(records_02.next().label());
}

// filters by two fields 
GET /data/contacts?type=a&isCustomer=false

// filters by a nested field 
var records_03 = sys.data.find('companies', {'addresses.state': 'CA'});
log('total: '+records_03.count());
while (records_03.hasNext()) {
    log(records_03.next().label());
}

// filters by a nested field 
var query_03 = sys.data.createQuery('companies')
    .field('addresses.state').equals('CA')
var records_03 = sys.data.find(query_03);
log('total: '+records_03.count());
while (records_03.hasNext()) {
    log(records_03.next().label());
}

// filters by a nested field 
GET /data/contacts?addresses.state=CA

// retrieves only name and type of customers 
var records_04 = sys.data.find('companies', {isCustomer: true, _fields: 'name,type'});
log('total: '+records_04.count());
while (records_04.hasNext()) {
    log(records_04.next().label());
}

// retrieves only name and type of customers 
var query_04 = sys.data.createQuery('companies')
    .field('isCustomer').equals(true).includeFields('name', 'type')
var records_04 = sys.data.find(query_04);
log('total: '+records_04.count());
while (records_04.hasNext()) {
    log(records_04.next().label());
}

// retrieves only name and type of customers 
GET /data/contacts?isCustomer=true&_fields=name,type

In the query map and REST API, parameters starting with _ are system parameters, while others are fields. The next section explains all the options for filtering by fields.

Filters by field #

You can filter results by field value. For instance, if your entity has a field called name, you can make the following query:

//  
var records_05 = sys.data.find('companies', {name: 'Blogpad'});
log('total: '+records_05.count());
while (records_05.hasNext()) {
    log(records_05.next().label());
}

//  
var query_05 = sys.data.createQuery('companies')
    .field('name').equals('Blogpad')
var records_05 = sys.data.find(query_05);
log('total: '+records_05.count());
while (records_05.hasNext()) {
    log(records_05.next().label());
}

//  
GET /data/contacts?name=Blogpad

You can also provide several values (comma-separated), and it will act like an OR:

//  
var records_06 = sys.data.find('companies', {name: 'Blogpad,Jazzy'});
log('total: '+records_06.count());
while (records_06.hasNext()) {
    log(records_06.next().label());
}

//  
var query_06 = sys.data.createQuery('companies')
    .field('name').equals('Blogpad,Jazzy')
var records_06 = sys.data.find(query_06);
log('total: '+records_06.count());
while (records_06.hasNext()) {
    log(records_06.next().label());
}

//  
GET /data/contacts?name=Blogpad,Jazzy

If the value you are matching already contains a comma, you should wrap it in double quotes:

//  
var records_07 = sys.data.find('companies', {name: '"ACME, Inc"'});
log('total: '+records_07.count());
while (records_07.hasNext()) {
    log(records_07.next().label());
}

//  
var query_07 = sys.data.createQuery('companies')
    .field('name').equals('"ACME, Inc"')
var records_07 = sys.data.find(query_07);
log('total: '+records_07.count());
while (records_07.hasNext()) {
    log(records_07.next().label());
}

//  
GET /data/contacts?name="ACME, Inc"

You can filter by nested fields as well:

//  
var records_08 = sys.data.find('companies', {'contactInformation.email': 'dwelchdp@apple.com'});
log('total: '+records_08.count());
while (records_08.hasNext()) {
    log(records_08.next().label());
}

//  
var query_08 = sys.data.createQuery('companies')
    .field('contactInformation.email').equals('dwelchdp@apple.com')
var records_08 = sys.data.find(query_08);
log('total: '+records_08.count());
while (records_08.hasNext()) {
    log(records_08.next().label());
}

//  
GET /data/contacts?contactInformation.email=dwelchdp@apple.com

If the nested group field is multi-valued, you can still perform a query like this:

//  
var records_09 = sys.data.find('companies', '{'_or': {'type': 'a', '_and': {'type': 'b', 'isCustomer': true}}}');
log('total: '+records_09.count());
while (records_09.hasNext()) {
    log(records_09.next().label());
}

//  
var query_09 = sys.data.createQuery('companies')
    '.query(function(q) { return q.or(q.field('type').equals('a'), q.and(q.field('type').equals('b'), q.field('isCustomer').equals(true))); })'
var records_09 = sys.data.find(query_09);
log('total: '+records_09.count());
while (records_09.hasNext()) {
    log(records_09.next().label());
}

//  
GET /data/contacts?_query=or(filter('type','a'),and(filter('type','b'),filter('isCustomer','true')))

If the entity has a geo point type, a near operator can be used to perform a query. The operator takes 4 parameters:

• latitude
• longitude
• minDistance(optional)
• maxDistance(optional)

// sorts companies by proximity to [ lat: 10, long: 22 ] that are at a min distance of 5000 m and at a maximum of 10000 m 
var records_sample = sys.data.find('companies', {'location':'near(5,10,5000,10000)'});
log('total: '+records_sample.count());
while (records_sample.hasNext()) {
    log(records_sample.next().label());
}

// sorts companies by proximity to [ lat: 10, long: 22 ] that are at a min distance of 5000 m and at a maximum of 10000 m 
var query_sample = sys.data.createQuery('companies')
    .field('location').near(5,10,5000,10000)
var records_sample = sys.data.find(query_sample);
log('total: '+records_sample.count());
while (records_sample.hasNext()) {
    log(records_sample.next().label());
}

// sorts companies by proximity to [ lat: 10, long: 22 ] that are at a min distance of 5000 m and at a maximum of 10000 m 
GET /data/contacts?near(5,10,5000,10000)

equals(value)
notEquals(value)
like(value)    // options: /value, value/ or /regexp/
between(from,to)
greater(value)
greaterOrEquals(value)
less(value)
lessOrEquals(value)
empty()
notEmpty()

Each field type supports different operators. Refer to the documentation for each type to see which operators are supported.

The following operators are available for all types:

equals(value)
notEquals(value)
empty()
notEmpty()

Complex queries #

Complex queries are similar to regular queries, but they allow you to mix and nest OR and AND expressions. They are separated because they require a different syntax, especially in the REST API and query builder.

Here’s a sample complex query, highlighting the differences between the three versions:

// Query on companies 
var records_10 = sys.data.find('companies', { _or: { type: 'a', _and: { type: 'b', isCustomer: true }}});
log('total: '+records_10.count());
while (records_10.hasNext()) {
    log(records_10.next().label());
}

// Query on companies 
var query_10 = sys.data.createQuery('companies')
    .field('type').equals('a').or().field('type').equals('b').field('isCustomer').equals(true)
var records_10 = sys.data.find(query_10);
log('total: '+records_10.count());
while (records_10.hasNext()) {
    log(records_10.next().label());
}

// Query on companies 
GET /data/contacts?_query=or(filter('type','a'),and(filter('type','b'),filter('isCustomer','true')))

The query map version is almost the same as simple queries; it uses the _or and _and operators. However, in the REST API and query builder, due to interface limitations, complex queries must be written differently.

For the REST API, you must pass a _query parameter, where you can use these operators:

• or(...): evaluates to true if any of the criteria passed as arguments is true. It accepts a list of filter criteria that can be and(), filter(), or another or().
• and(...): similar to or(), but it evaluates to true if all the criteria passed as arguments are true.
• filter(field,value): a simple field filter, like those in simple queries. Always enclose the name and value with single quotes. When using the query builder, you should use the query() method, which accepts a function as a parameter. This function receives a helper object and should return a query criteria.

Here are some examples of complex queries in the REST API and query builder:

// Query on companies 
var query_11 = sys.data.createQuery('companies')
    .and(q.field('isCustomer').equals(true), q.or(q.field('addresses.state').equals('CA'), q.field('numberOfEmployees').greater(100)))
var records_11 = sys.data.find(query_11);
log('total: '+records_11.count());
while (records_11.hasNext()) {
    log(records_11.next().label());
}

// Query on companies 
GET /data/contacts?_query=and(filter('isCustomer','true'),or(filter('addresses.state','CA'),filter('numberOfEmployees','greater(100)')))

// Query on companies 
var query_12 = sys.data.createQuery('companies')
    .or(q.field('isCustomer').equals(true), q.field('numberOfEmployees').greater(900), q.field('addresses.state').equals('CA,CO,NJ'))
var records_12 = sys.data.find(query_12);
log('total: '+records_12.count());
while (records_12.hasNext()) {
    log(records_12.next().label());
}

// Query on companies 
GET /data/contacts?_query=or(filter('isCustomer','true'),filter('numberOfEmployees','greater(900)'),filter('addresses.state','CA,CO,NJ'))

Query parameters #

In addition to field filtering, you can specify query parameters to control how the query is executed.

Limit size #

You can set a maximum number of records to fetch using the _size parameter or the size() method:

// fetches 10 records at most 
var records_13 = sys.data.find('companies', {_size: 10});
log('total: '+records_13.count());
while (records_13.hasNext()) {
    log(records_13.next().label());
}

// fetches 10 records at most 
var query_13 = sys.data.createQuery('companies')
    .size(10)
var records_13 = sys.data.find(query_13);
log('total: '+records_13.count());
while (records_13.hasNext()) {
    log(records_13.next().label());
}

// fetches 10 records at most 
GET /data/contacts?_size=10

It’s possible that the query returns fewer records than the specified number.

The REST and Javascript APIs will return the total number of records matched by the query. Please refer to the documentation for each API for more information.

Skip records #

You can provide an offset to skip some records in your query. You can use a number to indicate how many records to skip, or you can use an ID.

If you use a number, that number will indicate how many records to skip. Use the _offset parameter or the offset() method:

// skips the first 5 records and the first record retrieved will be the 6th one 
var records_14 = sys.data.find('companies', {_offset: 5});
log('total: '+records_14.count());
while (records_14.hasNext()) {
    log(records_14.next().label());
}

// skips the first 5 records and the first record retrieved will be the 6th one 
var query_14 = sys.data.createQuery('companies')
    .offset(5)
var records_14 = sys.data.find(query_14);
log('total: '+records_14.count());
while (records_14.hasNext()) {
    log(records_14.next().label());
}

// skips the first 5 records and the first record retrieved will be the 6th one 
GET /data/contacts?_offset=5

If there are fewer records than the specified offset, the query will return no results.

The other option for specifying an offset is an ID. This form can only be used if you are either explicitly sorting by the id field or if you have omitted sorting options in your query (the default is id).

For instance, if you query 5 records and receive the following record IDs:

[
  '57fd2d65e4b0ce322b0c8665',
  '57fd2d65e4b0ce322b0c8565',
  '57fd2d65e4b0ce322b0c8547',
  '57fd2d65e4b0ce322b0c84b1',
  '57fd2d64e4b0ce322b0c8349'
]

To fetch the next 5 records, you should make the following query, using the last record’s ID as the offset:

// starts after record with ID 57fd2d64e4b0ce322b0c8349 
var records_15 = sys.data.find('companies', {_offset: '57fd2d64e4b0ce322b0c8349'});
log('total: '+records_15.count());
while (records_15.hasNext()) {
    log(records_15.next().label());
}

// starts after record with ID 57fd2d64e4b0ce322b0c8349 
var query_15 = sys.data.createQuery('companies')
    .offset('57fd2d64e4b0ce322b0c8349')
var records_15 = sys.data.find(query_15);
log('total: '+records_15.count());
while (records_15.hasNext()) {
    log(records_15.next().label());
}

// starts after record with ID 57fd2d64e4b0ce322b0c8349 
GET /data/contacts?_offset=57fd2d64e4b0ce322b0c8349

This query will not return the record with ID 57fd2d64e4b0ce322b0c8349 but rather the next 5 records after it.

Sorting #

You can sort results based on a field. By default, the id field and desc direction are used if no sorting options are specified. However, you can change this behavior using the _sortField and _sortType parameters or the sortBy() method:

//  
var records_16 = sys.data.find('companies', { state: 'active', _sortField: 'name', _sortType: 'asc' });
log('total: '+records_16.count());
while (records_16.hasNext()) {
    log(records_16.next().label());
}

//  
var query_16 = sys.data.createQuery('companies')
    .field('state').equals('active').sortBy('name', 'asc')
var records_16 = sys.data.find(query_16);
log('total: '+records_16.count());
while (records_16.hasNext()) {
    log(records_16.next().label());
}

//  
GET /data/contacts?state=active&_sortField=name&_sortType=asc

You can even use a nested field for sorting:

//  
var records_17 = sys.data.find('companies', { _sortField: 'contactInformation.email', _sortType: 'asc' });
log('total: '+records_17.count());
while (records_17.hasNext()) {
    log(records_17.next().label());
}

//  
var query_17 = sys.data.createQuery('companies')
    .sortBy('contactInformation.email', 'asc')
var records_17 = sys.data.find(query_17);
log('total: '+records_17.count());
while (records_17.hasNext()) {
    log(records_17.next().label());
}

//  
GET /data/contacts?_sortField=contactInformation.email&_sortType=asc

The _sortField parameter specifies the field to sort by, and _sortType specifies the sorting order, which can be one of the following:

• asc (ascending)
• desc (descending)

Keep in mind that you cannot use multi-valued or nested fields as the sorting field.

So far you cannot specify many fields for sorting. In some cases you can achieve the same result by adding a calculated field that concatenates the fields you want to use for sorting.

Include fields #

By default, all fields are returned in the results. If you only want to retrieve specific fields, you can use the _fields parameter or the includeFields() method:

// only name and email fields will be fetched 
var records_18 = sys.data.find('companies', { _fields: 'name,email' });
log('total: '+records_18.count());
while (records_18.hasNext()) {
    log(records_18.next().label());
}

// only name and email fields will be fetched 
var query_18 = sys.data.createQuery('companies')
    .includeFields('name', 'email')
var records_18 = sys.data.find(query_18);
log('total: '+records_18.count());
while (records_18.hasNext()) {
    log(records_18.next().label());
}

// only name and email fields will be fetched 
GET /data/contacts?_fields=name,email

You can select nested fields like addresses.state. If you put the parent field, like addresses, all inner fields will be included.

// you can also indicate nested fields 
var records_19 = sys.data.find('companies', { _fields: 'name,addresses.state' });
log('total: '+records_19.count());
while (records_19.hasNext()) {
    log(records_19.next().label());
}

// you can also indicate nested fields 
var query_19 = sys.data.createQuery('companies')
    .includeFields('name', 'addresses.state')
var records_19 = sys.data.find(query_19);
log('total: '+records_19.count());
while (records_19.hasNext()) {
    log(records_19.next().label());
}

// you can also indicate nested fields 
GET /data/contacts?_fields=name,addresses.state

System field will always be returned (id, version, label).

Fetch related records #

If you wish to reduce the number of requests to the server, you can retrieve a record and its related records by using the _relationshipsToFetch parameter. This parameter accepts a comma-separated list of relationship fields.

// Fetches the primary records along with company information 
GET /data/contacts?_relationshipsToFetch=company&_size=10

// You can specify multiple relationship fields separated by commas, even if the relationship field is multi-valued 
GET /data/contacts?_relationshipsToFetch=company,skills&_size=10

Global search #

When querying using the global search feature, it will attempt to match the provided string in any field within the entity, rather than targeting a specific field. For example:

// Finds records where any field contains 'lacinia' or 'erat' 
var records_22 = sys.data.find('companies', { _globalSearch: 'lacinia erat' });
log('total: '+records_22.count());
while (records_22.hasNext()) {
    log(records_22.next().label());
}

// Finds records where any field contains 'lacinia' or 'erat' 
var query_22 = sys.data.createQuery('companies')
    .globalSearch('lacinia erat')
var records_22 = sys.data.find(query_22);
log('total: '+records_22.count());
while (records_22.hasNext()) {
    log(records_22.next().label());
}

// Finds records where any field contains 'lacinia' or 'erat' 
GET /data/contacts?_globalSearch=lacinia erat

Note that to match the entire phrase, you should enclose it with double quotes:

// finds record where any field has the tense 'lacinia erat' 
var records_23 = sys.data.find('companies', { _globalSearch: '"lacinia erat"' });
log('total: '+records_23.count());
while (records_23.hasNext()) {
    log(records_23.next().label());
}

// finds record where any field has the tense 'lacinia erat' 
var query_23 = sys.data.createQuery('companies')
    .globalSearch('"lacinia erat"')
var records_23 = sys.data.find(query_23);
log('total: '+records_23.count());
while (records_23.hasNext()) {
    log(records_23.next().label());
}

// finds record where any field has the tense 'lacinia erat' 
GET /data/contacts?_globalSearch="lacinia erat"

Please be aware that all searches are case-insensitive.

Format #

In the REST API, you can utilize the _format parameter to specify the format of the returned records. There are two options:

• native: Returns the “raw” values for each field. For example, a date-time field will be returned as milliseconds. This is the default format.
• plainText: Returns field values converted to strings using the display options of each field. For example, a date-time field will be returned as a string based on the field’s display options.

The plainText format is useful when you need to display information to users in an external application outside of the Slingr runtime, and you do not want to handle formatting.

// Companies query sample 
GET /data/contacts?_format=plainText&_size=10

Format using one specific user’s settings #

This can be achieved using the _formatForUser parameter and is only meaningful when using the plainText option in _format.

You should provide either the user’s ID or email that will be used to format the record. For instance, if the user has configured a different time zone, date-time fields will use that setting to format dates. This is useful when you need to send emails to individuals residing in different time zones.

// Companies query sample 
GET /data/contacts?_format=plainText&_formatForUser=test1.docs@slingr.io&_size=10