Describe the query language, which is shared by the REST API as well as the Javascript API.

Overview

The query language allows to find records in an entity using complex criteria. This language is shared by the REST API as well as the Javascript API, however keep in mind that the way you specify queries will be different. For example a simple query to retrieve 10 active companies using the REST API looks like this:

GET https://app.slingrs.io/prod/runtime/api/data/companies?state=active&_size=10

while in the Javascript API it would be something like this:

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

In the Javascript API it is also possible to use the query builder) which provides some helpers to build these queries:

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

Usually when you have more complex query criteria the query builder is easier to use than the simple map form as it provides conversion and parsing features. Also there are queries that cannot be expressed in the query map version. For example:

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 in using the query map version because you cannot use the same key in the map. It would be something like this:

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

So in that case the key name is twice in the map and won’t work as expected.

Another thing to take into account is exception handling. When you use the query builder some exception will be thrown when you are building the query. 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 above script will throw an exception when calling the method field() because field doesNotExist is not a valid field in the companies entity. The same will happen with other bad query configurations, so you need to keep in mind that exceptions could be thrown during the building of the query.

On the other hand, when using the query map, you won’t get any exception when building the query but when executing it:

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

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

Here you will find all samples in the three versions so you can compare them. If something only applies to some versions it will be explicitly indicated.

Simple queries

Simple queries allow to filter records using AND logical operator only. You can still indicate many filters. Here there are some simple queries:

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

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

finds a company by exact name

GET /data/companies?name=Blogpad

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

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

filters by companies with more than 200 employees; limits results to 10 and use offset of 20

GET /data/companies?numberOfEmployees=greater(200)&_size=10&_offset=20

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

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

filters by two fields

GET /data/companies?type=a&isCustomer=false

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

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

filters by a nested field

GET /data/companies?addresses.state=CA

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

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

retrieves only name and type of customers

GET /data/companies?isCustomer=true&_fields=name,type

In the query map and REST API, parameters starting with _ are system parameters, while others are fields. Next section explains all options to filter by fields.

Filters by field

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

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

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

GET /data/companies?name=Blogpad

Also you could provide several values (comma separated) and it is going to act like an OR:

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

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

GET /data/companies?name=Blogpad,Jazzy

Keep in mind that if the value you are matching already has a comma you will need to wrap it using double quotes:

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

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

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

You are able to filter by nested fields as well:

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

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

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

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

// finds all records where at least one of the addresses' state is 'CA'
var records = sys.data.find('companies', {'addresses.state': 'CA'});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

// finds all records where at least one of the addresses' state is 'CA'
var query = sys.data.createQuery('companies')
    .field('addresses.state').equals('CA');
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

finds all records where at least one of the addresses' state is 'CA'

GET /data/companies?addresses.state=CA

You can filter by different fields:

var records = sys.data.find('companies', {
  'addresses.state': 'CA', 
  state: 'active'
});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

var query = sys.data.createQuery('companies')
    .field('addresses.state').equals('CA')
    .field('state').equals('active');
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

GET /data/companies?addresses.state=CA&state=active

If you need to filter nested record fields simultaneously you should do the following:

// filters by street and zip code
var records = sys.data.find('companies', {'addresses': {'street': 'Street 123', 'zipCode': '1234'}});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

// filters by street and zip code
var query = sys.data.createQuery('companies')
    .field('addresses').equals({'street': 'Street 123', 'zipCode': '1234'});
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

filters by street and zip code

GET /data/companies?addresses={"street": "Street 123", "zipCode": "1234"}

Additionally, you can specify different query operators:

var records = sys.data.find('companies', {
  name: 'like(al)', 
  numberOfEmployees: 'between(100, 500)',
  type: 'notEquals(a)'
});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

var query = sys.data.createQuery('companies')
    .field('name').like('al')
    .field('numberOfEmployees').between(100, 500)
    .field('type').notEquals('a');
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

GET /data/companies?name=like(al)&numberOfEmployees=between(100,500)&type=notEquals(a)

This is an example for using the starts with shortcut:

var records = sys.data.find('contacts', {
  lastName: 'like(/s)'
});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

var query = sys.data.createQuery('contacts')
    .field('lastName').like('/s');
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

GET /data/contacts?lastName=like(/s)

We also have an ends with shortcut:

var records = sys.data.find('contacts', {
  lastName: 'like(n/)'
});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

var query = sys.data.createQuery('contacts')
    .field('lastName').like('n/');
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

GET /data/contacts?lastName=like(n/)

For more complex cases we can also use a regular expression:

var records = sys.data.find('contacts', {
  lastName: 'like(/^S.*|.*n$/)'
});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

var query = sys.data.createQuery('contacts')
    .field('lastName').like('/^S.*|.*n$/');
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

GET /data/contacts?lastName=like(/^S.*|.*n$/)

These are the available operators:

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 allows different operators. You should check each type’s documentation to see which operators are supported.

The following operators are available for all types:

equals(value)
notEquals(value)
empty()
notEmpty()
If you don’t specify any operator, the default one is equals()
It is always a good idea to check each type’s documentation when you need to filter by field values. Each type supports different ways to query and might accept alternative formats to simplify its usage.

Complex queries

Complex queries are just regular queries but you can mix and nest OR and AND expressions. The reason why they are separated is that they require a different syntax to be written, especially in the REST API and query builder.

Here is a sample of a complex query and you can see the differences between the three versions:

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

var query = 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 = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

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

The query map version is almost the same as simple queries. It just uses the _or and _and operators. However in the REST API and query builder, due to some limitations in the interface, complex queries need to be written in a different way.

For the REST API you will have to 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 just another or().
  • and(...): same as or(), but it will evaluate to true if all the criteria passed as arguments are true.
  • filter(field,value): this is just a simple field filter as the ones in simple queries. You should always enclose name and value with single quotes.

When using the query builder you will need to use the query() method, which accepts a function as parameter. That function will get a helper object and should return a query criteria.

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

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

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

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

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

Query parameters

Apart from filtering by fields you can also specify some parameters to control how the query is performed.

Limit size

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

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

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

fetches 10 records at most

GET /data/companies?_size=10

It is possible that the query returns less than the specified number of records.

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

For the REST API the maximum value you can send is 1,000. The default value is 20 if not specified. These limits and defaults do not apply to the Javascript API because a cursor is used to iterate over the results.

Skip records

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

If you use a number, that number will indicate how many records to skip. You should 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 = sys.data.find('companies', {_offset: 5});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

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

skips the first 5 records and the first record retrieved will be the 6th one

GET /data/companies?_offset=5

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

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

For example, you query 5 records and you get the following record IDs:

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

Then, if you want to fetch the next 5 records, you should make the following query (using the last record’s ID as offset):

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

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

starts after record with ID 57fd2d64e4b0ce322b0c8349

GET /data/companies?_offset=57fd2d64e4b0ce322b0c8349

This query is not going to return record with ID 57fd2d64e4b0ce322b0c8349, but the next 5 records after it.

If you need to iterate over all records using the REST API and you want to avoid fetching the same record twice you probably want to use IDs as offsets. If you use numbers it is possible the the modifications of records affects your query and you might process the same record twice or miss some of them.

Sorting

It is possible to sort results based on a field. By default we use the id field and desc direction if no sorting options are indicated, but you can change that using the _sortField and _sortType parameters or the sortBy() method:

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

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

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

You can even use a nested field for sorting:

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

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

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

Sorting direction can be asc (ascending) or desc (descending). By default desc will be used if not indicated.

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.

Select fields to fetch

By default all fields will be fetched. If you want to specify which fields you want to fetch, you can pass the _fields parameter or use the includeFields() method:

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

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

only name and email fields will be fetched

GET /data/companies?_fields=name,type

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 = sys.data.find('companies', {_fields: 'name,addresses.state'});
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

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

you can also indicate nested fields

GET /data/companies?_fields=name,addresses.state

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

This parameter only works on the REST API. It won’t have any effect if you use it on the Javascript API.

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

fetches the main records and also fetch the information of the company

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
This is only allowed for entities with global search enabled.

When querying using the global search feature it will try to match the string in any field on the entity instead of trying to match one specific field. For example:

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

// finds records where any field has either the word 'lacinia'' or 'erat'
var query = sys.data.createQuery('companies')
    .globalSearch('lacinia erat');
var records = sys.data.find(query);
log('total: '+records.count());
while (records.hasNext()) {
  log(records.next().label());
}

finds records where any field has either the word 'lacinia'' or 'erat'

GET /data/companies?_globalSearch=lacinia erat

Notice that in order to match the whole sentence you need to enclose it with double quotes:

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

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

finds record where any field has the tense 'lacinia erat'

GET /data/companies?_globalSearch="lacinia erat"

Keep in mind that all searches are case insensitive.

Format

This parameter only works on the REST API. It won’t have any effect if you use it on the Javascript API.

In the REST API you can use the _format parameter to format the 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 values of fields converted to string using the display option of each field. For example, a date time field will be returned as a string based on the display options of the field.

The plainText format is useful when you need to display information to the user in an external app outside Slingr runtime and you don’t want to take care of formatting.

GET /data/companies?_format=plainText&_size=10

Format using one specific user’s settings

This can be done using the _formatForUser parameter and only makes sense if you use the option plainText in _format.

You should pass either the user’s ID or email that will be used to format the record. For example if the user configured a different time zone, date time fields will use that setting to format dates. This is useful if you need to send emails to individuals that live across different time zones.

GET /data/companies?_format=plainText&_formatForUser=test1.docs@slingr.io&_size=10
Back to top