Advanced Queries
A number of application resources can be retrieved using an advanced query, which allows for finding resources that meet more criteria than can be set through traditional query methods.
Supported Resources
Resources that support advanced queries will display the advanced query panel wherever the resources are being used. Currently, advanced queries are enabled for the following resources:
- Devices
- Events
- Data Table Rows
- Experience Groups (Resource Jobs and Group: Get Node)
- Experience Users (Resource Jobs and User: Get Node)
- Workflows and Workflow Versions (API only)
- Access Keys (API only)
- Instance Organizations (API only)
Support for additional resources is being rolled out across the Losant platform over the coming months. If you have a use case for an advanced query on a resource that does not yet support it, please let us know in our forums.
Building Queries
To build a new advanced query, or edit one that is currently applied, click the “Edit Filters” icon in the top right corner of the query preview box. Doing so will display a modal in which you can write your new query.
Simple Mode
Most advanced queries are built in the feature’s “Simple Mode”, which allows you to define a series of individual queries line by line and choose to return resources that match either any, all, or none of those queries. To build your query in Simple Mode, in the top selector, choose one of:
- Match ALL of the following … (equivalent to the
$and
logical operator).
If ALL of the individual queries match against an item, that item will be returned. If there are NO filters, ALL items will be returned. - Match ANY of the following … (equivalent to the
$or
logical operator). If ANY of the individual queries match against an item, that item will be returned. If there are NO filters, ALL items will be returned. - Match NONE of the following … (equivalent to the
$nor
logical operator).
If ALL of the individual queries DO NOT match against an item, that item will be returned. If there are NO filters, NO items will be returned.
Note: If you are switching from an Advanced Mode query to one of the Simple Mode queries, your existing advanced query will be removed and you will need to build the new query from scratch. However, switching between the two Simple Mode options will maintain any simple queries you have built so far.
Advanced Mode
If you require a more specific query than can be built using Simple Mode—whether you are utilizing query properties not supported in Simple Mode or are nesting logical operators—you may switch to “Advanced Mode”. In the top selector, select Advanced … to switch to this mode.
Here, you can build your query as a JSON object similar in form to MongoDB’s find() syntax. All the comparison operators described below, as well as the logical operators, are available when building an advanced query.
Note: If you are switching from one of the Simple Mode queries to Advanced Mode, your existing query will be converted into its Advanced Mode equivalent. In many cases, this allows you to build the majority of your query in Simple Mode and then apply a few additional parameters.
Resetting Queries
If at any time you wish to remove your advanced query and revert to the default value, click the “Reset to Default” button in the query preview box or within the modal. Note that the default query can vary depending both on the resource being queried and where the query is being applied.
Logical Operators
Logical operators allow for combining multiple sets of query rules in order to return the desired results. In Simple Mode, these operators exist at the top level of the query and wrap all of the property comparisons to create the “Match Any / All / None” behavior.
In Advanced Mode, logical operators can exist at any level in the query, and multiple operators can be applied at multiple levels. The value of a logical operator must be an array containing at least one property comparison.
For demonstration purposes, we will use this sample data set of seven devices, each with a unique name and device class …
[
{ "name": "A Standalone Device", "deviceClass": "standalone" },
{ "name": "A Gateway Device", "deviceClass": "gateway" },
{ "name": "An Edge Compute Device", "deviceClass": "edgeCompute" },
{ "name": "An Embedded Device", "deviceClass": "embedded" },
{ "name": "A Peripheral Device", "deviceClass": "peripheral" },
{ "name": "A Floating Device", "deviceClass": "floating" },
{ "name": "A System Device", "deviceClass": "system" }
]
$and (Match All)
In order for a record to be returned, all of the property comparisons within this logical operator must return true
.
Given this query …
{
"$and": [
{ "name": { "$contains": "y" } },
{ "deviceClass": { "$in": ["gateway", "edgeCompute"] } }
]
}
… the following devices would be returned …
[
{ "name": "A Gateway Device", "deviceClass": "gateway" } // matches both property comparisons
]
$or (Match Any)
In order for a record to be returned, at least one of the property comparisons within this logical operator must return true
.
Given the same query, but wrapped with the $or
operator instead of $and
…
{
"$or": [
{ "name": { "$contains": "y" } },
{ "deviceClass": { "$in": ["gateway", "edgeCompute"] } }
]
}
… the following devices would be returned …
[
{ "name": "A Gateway Device", "deviceClass": "gateway" }, // matches both property comparisons
{ "name": "An Edge Compute Device", "deviceClass": "edgeCompute" }, // matches the deviceClass comparison
{ "name": "A System Device", "deviceClass": "system" } // matches the name comparison
]
$nor (Match None)
In order for a record to be returned, none of the property comparisons within this logical operator must return true
.
Given our same query, but wrapped with the $nor
operator …
{
"$nor": [
{ "name": { "$contains": "y" } },
{ "deviceClass": { "$in": ["gateway", "edgeCompute"] } }
]
}
… the following devices would be returned …
[
{ "name": "A Standalone Device", "deviceClass": "standalone" },
{ "name": "An Embedded Device", "deviceClass": "embedded" },
{ "name": "A Peripheral Device", "deviceClass": "peripheral" },
{ "name": "A Floating Device", "deviceClass": "floating" }
]
Property Comparison Types
Given a resource’s property value and a given query value, there are a number of comparison types against which to build your advanced queries.
ID Comparisons
These comparisons are useful for targeting specific resources by ID, or for querying resources that are associated with another application resource. For example, event queries can be written to only return events that are associated with a specific device.
While ID values should always be passed in lowercase form, these queries are actually case insensitive; for example, a property value of “abc” and a query value of “ABC” will be treated as equal.
The following operators are supported:
$eq
: The ID values must match.$ne
: The IDs must not match.
Depending on the property being queried, you may also pass a null
comparison to determine whether the ID exists on the resource. In addition, when querying the id
property of a resource, you may use the comparison operators $gt
, $lt
, $gte
, and $lte
.
Numerical Comparisons
For these comparisons, both values must be numbers or the query will error and will fail to return any results.
The following operators are supported:
$eq
: The property and query values must exactly match.$ne
: The property and query values must not match.$gt
: The property value must be greater than the query value.$lt
: The property value must be less than the query value.$gte
: The property value must be greater than or equal to the query value.$lte
: The property value must be less than or equal to the query value.
String Comparisons
Both the property value and the query value must be strings, or the query will error and will fail to return any results.
The following operators are supported:
$eq
: The two strings must exactly match, including casing. For example, an$eq
query will not match when comparing “Abc” and “abc”.$ne
: The property and query values must not match, including casing. For example, a$ne
query will match when comparing “Abc” and “abc”.$startsWith
: The property value must begin with the characters in the query value. This will also match if the two values are equal. Case insensitivity is optional.$endsWith
: The property value must end with the characters in the query value. This will also match if the two values are equal. Case insensitivity is optional.$contains
: The property value must contain, in sequence, the characters in the query value. This will also match if the two values are equal. Case insensitivity is optional.
Note: When building case-insensitive queries in Advanced Mode, the option is passed alongside the operator. For example:
{
"stringPropertyName": {
"$startsWith": "abc",
"$ci": true
},
"anotherProperty": {
"$contains": "def",
"$ci": false
}
}
Enumeration Comparisons
A property value is compared against a query value, and the query value may only resolve to one of a set of predetermined options. Those options depend on the resource and property being queried. For example, when querying for devices by device class, only one of the supported device classes may be provided as the query value. Providing a query value that is not valid for the resource and property will cause the query to error.
Most enumeration queries only support these operators:
$eq
: The property and query values must match.$ne
: The property and query values must not match.
In some cases, numerical operators can also be applied to these comparisons to return, for example, values that are “at or above / below” a given value:
- Event levels:
critical
>error
>warning
>info
- Event states:
resolved
>acknowledged
>new
Date Comparisons
These comparisons are usually applied against the creation or last update time of a resource. Query values should be defined either as a Unix timestamp in milliseconds or as an ISO Date string. Prior to comparing the dates, the property value and query value will both be cast to a timestamp.
The following operators are supported:
$eq
: The two dates must exactly match, down to the millisecond.$ne
: The two dates must not match.$gt
: The property value must be greater (later) than the query value.$lt
: The property value must be less (earlier) than the query value.$gte
: The property value must be greater (later) than or equal to the query value.$lte
: The property value must be less (earlier) than or equal to the query value.
When building queries that support templates, dates must be typed out explicitly or rendered via a template. If the query does not support templates, you may use a date and time picker to choose the query value.
Note: Take care when writing date queries, as the property and query values will be compared down to the millisecond. Therefore, doing $eq
and $ne
queries is discouraged as they can often lead to unexpected results.
Tag Comparisons
Many resources also support comparing tags as query values against a given resource’s tags (key/value pairs). For the query value:
- If a key and a value are provided, the comparison is made against that exact key and value pair being defined on the resource.
- If only a key is provided, the resource is checked for the existence of any tag with that key, regardless of its value.
- If only a value is provided, the resource is checked for the existence of any tag with that value, regardless of its key.
Tag comparisons are case-sensitive. The following operators are supported:
$eq
: The key, value, or key/value combination must exist on the resource.$ne
: The key, value, or key/value combination must not exist on the resource.
Note: When translating a tag comparison built in Simple Mode to Advanced Mode, a tag query will take the following form:
{
"tagPropertyName": {
"$eq": {
"$tagKey": "aKey",
"$tagValue": "A value"
}
}
}
To only check for the existence of a key or a value, simply do not define the other property in your query, as in the second line in the Simple Mode query above.
Tag Set Comparisons
In Advanced Mode, you may also provide a Set Comparison for tag keys and/or tag values. For example, the two queries below are logically identical and would return all devices with a tag key of “favoriteColor” and a value of either “red”, “green”, or “blue” …
{
"tags": {
"$eq": {
"$tagKey": "favoriteColor",
"$tagValue": {
"$in": ["red", "green", "blue"]
}
}
}
}
{
"tags": {
"favoriteColor": {
"$in": ["red", "green", "blue"]
}
}
}
The $nin
operator works in tag queries as well. The following would return all devices with a “favoriteColor” tag and any value other than “red”, “green”, or “blue” …
{
"tags": {
"favoriteColor": {
"$nin": ["red", "green", "blue"]
}
}
}
Set Comparisons also work on tag keys. The following query would return any device with a “favoriteColor” tag or a “favoriteMonth” tag, regardless of the tag value …
{
"tags": {
"$tagKey": {
"$in": ["favoriteColor", "favoriteMonth"]
}
}
}
Boolean Comparisons
These simply check that a given property value is either true
or false
.
The following operators are supported:
$eq
: The property value and query value are bothtrue
or bothfalse
.$ne
: The property value and query value are not bothtrue
or bothfalse
.
Note: Boolean comparisons do not compare values as either truthy or falsy. When running a boolean comparison, the values being compared must explicitly be true
or false
.
Null Comparisons
Some resource properties allow for doing null
comparisons, which is a way of determining whether a value exists at all. For example, if you wish to return all events that are associated with any device, you could run a query to find events whose deviceId
property is not null
.
When building queries in Simple Mode, null
comparisons are represented as equality operators reading null
values. For example:
{
"parentId": {
"$eq": null
},
"gatewayId": {
"$ne": null
}
}
is NULL
: The given property does not have a value set.is not NULL
: The given property does have a value set.
Note: When translating to queries built in Advanced Mode, these operators are represented as the following:
is NULL
:{ "parentId": { "$eq": null } }
is not NULL
:{ "gatewayId": { "$ne": null } }
Set Comparisons
These comparisons are for determining whether a given property value is one of a number of different values ($in
), or not one of a set of values ($nin
). The query value should be an array of strings or numbers, and the property value’s type should match the types within the query value.
The following operators are available:
is one of
: The property value must exactly match one of the values in the array, including casing.is not one of
: The property value must not match any of the values in the array.
Note: When translating to queries built in Advanced Mode, these operators are represented as the following:
is one of
:{ "deviceClass": { "$in": ["edgeCompute", "gateway"] } }
is not one of
:{ "name": { "$nin": ["Device A", "Device B"] } }
Usage
Advanced queries can be used in a number of places throughout the Losant platform:
- In resource lists, when simply viewing a collection of your application’s resources
- In workflow nodes, when building queries for retrieving or bulk updating items
- In dashboard blocks, when determining which resources to use when building out your visualizations
- In notebook inputs, when scoping your data down to a subset of resources for analysis
Templates in Queries
In many cases, advanced queries support the use of templates when defining your properties and values. The templates will be rendered against your workflow payload, dashboard context, or notebook context, and the resulting query will be used to return your results.
When using templates for your property definitions, all Simple Mode operators are available for use since the resolved property name is not known, and thus the comparison type cannot be determined. Similarly, some comparisons - such as tag comparisons - cannot be built in Simple Mode when defining a property as a template. For this reason, we recommend only using templates in your query values so that the query building interface can be more helpful when validating your queries prior to submission.
Was this page helpful?
Still looking for help? You can also search the Losant Forums or submit your question there.