IoT Hub query language for device and module twins, jobs, and message routing

Άρθρο
03/17/2023

IoT Hub provides a powerful SQL-like language to retrieve information regarding device twins, module twins, jobs, and message routing. This article presents:

An introduction to the major features of the IoT Hub query language, and
The detailed description of the language. For details on query language for message routing, see queries in message routing.

For specific examples, see Queries for device and module twins or Queries for jobs.

Note

Some of the features mentioned in this article, like cloud-to-device messaging, device twins, and device management, are only available in the standard tier of IoT Hub. For more information about the basic and standard/free IoT Hub tiers, see Choose the right IoT Hub tier for your solution.

Run IoT Hub queries

You can run queries against your IoT hub directly in the Azure portal.

Sign in to the Azure portal and navigate to your IoT hub.
Select Queries from the Device management section of the navigation menu.
Enter your query in the text box and select Run query.

You also can run queries within your applications using the Azure IoT service SDKs and service APIs.

For example code implementing IoT Hub queries, see the Query examples with the service SDKs section.

For links to SDK reference pages and samples, see Azure IoT SDKs.

Basics of an IoT Hub query

Every IoT Hub query consists of SELECT and FROM clauses, with optional WHERE and GROUP BY clauses.

Queries are run on a collection of JSON documents, for example device twins. The FROM clause indicates the document collection to be iterated on (either devices, devices.modules, or devices.jobs).

Then, the filter in the WHERE clause is applied. With aggregations, the results of this step are grouped as specified in the GROUP BY clause. For each group, a row is generated as specified in the SELECT clause.

SELECT <select_list>
  FROM <from_specification>
  [WHERE <filter_condition>]
  [GROUP BY <group_specification>]

SELECT clause

The SELECT <select_list> clause is required in every IoT Hub query. It specifies what values are retrieved from the query. It specifies the JSON values to be used to generate new JSON objects. For each element of the filtered (and optionally grouped) subset of the FROM collection, the projection phase generates a new JSON object. This object is constructed with the values specified in the SELECT clause.

For example:

Return all values
```
SELECT *
```
Return specific properties
```
SELECT DeviceID, LastActivityTime
```
Aggregate the results of a query to return a count
```
SELECT COUNT() as TotalNumber
```

Currently, selection clauses different than SELECT are only supported in aggregate queries on device twins.

The following syntax is the grammar of the SELECT clause:

SELECT [TOP <max number>] <projection list>

<projection_list> ::=
    '*'
    | <projection_element> AS alias [, <projection_element> AS alias]+

<projection_element> :==
    attribute_name
    | <projection_element> '.' attribute_name
    | <aggregate>

<aggregate> :==
    count()
    | avg(<projection_element>)
    | sum(<projection_element>)
    | min(<projection_element>)
    | max(<projection_element>)

Attribute_name refers to any property of the JSON document in the FROM collection.

FROM clause

The FROM <from_specification> clause is required in every ioT Hub query. It must be one of three values:

devices to query device twins
devices.modules to query module twins
devices.jobs to query job per-device details

For example:

Retrieve all device twins
```
SELECT * FROM devices
```

WHERE clause

The WHERE <filter_condition> clause is optional. It specifies one or more conditions that the JSON documents in the FROM collection must satisfy to be included as part of the result. Any JSON document must evaluate the specified conditions to "true" to be included in the result.

For example:

Retrieve all jobs that target a specific device

SELECT * FROM devices.jobs
  WHERE devices.jobs.deviceId = 'myDeviceId'

The allowed conditions are described in the expressions and conditions section.

GROUP BY clause

The GROUP BY <group_specification> clause is optional. This clause executes after the filter specified in the WHERE clause, and before the projection specified in the SELECT. It groups documents based on the value of an attribute. These groups are used to generate aggregated values as specified in the SELECT clause.

For example:

Return the count of devices that are reporting each telemetry configuration status

SELECT properties.reported.telemetryConfig.status AS status,
  COUNT() AS numberOfDevices
FROM devices
GROUP BY properties.reported.telemetryConfig.status

Currently, the GROUP BY clause is only supported when querying device twins.

Caution

The term group is currently treated as a special keyword in queries. In case, you use group as your property name, consider surrounding it with double brackets to avoid errors, e.g., SELECT * FROM devices WHERE tags.[[group]].name = 'some_value'.

The formal syntax for GROUP BY is:

GROUP BY <group_by_element>
<group_by_element> :==
    attribute_name
    | < group_by_element > '.' attribute_name

Attribute_name refers to any property of the JSON document in the FROM collection.

Query results pagination

A query object is instantiated with a max page size of less than or equal to 100 records. To obtain multiple pages, call the nextAsTwin on Node.js SDK or GetNextAsTwinAsync on .Net SDK method multiple times. A query object can expose multiple Next values, depending on the deserialization option required by the query. For example, a query object can return device twin or job objects, or plain JSON when using projections.

Expressions and conditions

At a high level, an expression:

Evaluates to an instance of a JSON type (such as Boolean, number, string, array, or object).
Is defined by manipulating data coming from the device JSON document and constants using built-in operators and functions.

Conditions are expressions that evaluate to a Boolean. Any constant different than Boolean true is considered as false. This rule includes null, undefined, any object or array instance, any string, and the Boolean false.

The syntax for expressions is:

<expression> ::=
    <constant> |
    attribute_name |
    <function_call> |
    <expression> binary_operator <expression> |
    <create_array_expression> |
    '(' <expression> ')'

<function_call> ::=
    <function_name> '(' expression ')'

<constant> ::=
    <undefined_constant>
    | <null_constant>
    | <number_constant>
    | <string_constant>
    | <array_constant>

<undefined_constant> ::= undefined
<null_constant> ::= null
<number_constant> ::= decimal_literal | hexadecimal_literal
<string_constant> ::= string_literal
<array_constant> ::= '[' <constant> [, <constant>]+ ']'

To understand what each symbol in the expressions syntax stands for, refer to the following table:

Symbol	Definition
attribute_name	Any property of the JSON document in the FROM collection.
binary_operator	Any binary operator listed in the Operators section.
function_name	Any function listed in the Functions section.
decimal_literal	A float expressed in decimal notation.
hexadecimal_literal	A number expressed by the string '0x' followed by a string of hexadecimal digits.
string_literal	Unicode strings represented by a sequence of zero or more Unicode characters or escape sequences. String literals are enclosed in single quotes or double quotes. Allowed escapes: `\'`, `\"`, `\\`, `\uXXXX` for Unicode characters defined by four hexadecimal digits.

Operators

The following operators are supported:

Family	Operators
Arithmetic	+, -, *, /, %
Logical	AND, OR, NOT
Comparison	=, !=, <, >, <=, >=, <>

Functions

When querying twins and jobs the only supported function is:

Function	Description
IS_DEFINED(property)	Returns a Boolean indicating if the property has been assigned a value (including `null`).

In routes conditions, the following math functions are supported:

Function	Description
ABS(x)	Returns the absolute (positive) value of the specified numeric expression.
EXP(x)	Returns the exponential value of the specified numeric expression (e^x).
POWER(x,y)	Returns the value of the specified expression to the specified power (x^y).
SQUARE(x)	Returns the square of the specified numeric value.
CEILING(x)	Returns the smallest integer value greater than, or equal to, the specified numeric expression.
FLOOR(x)	Returns the largest integer less than or equal to the specified numeric expression.
SIGN(x)	Returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.
SQRT(x)	Returns the square root of the specified numeric value.

In routes conditions, the following type checking and casting functions are supported:

Function	Description
AS_NUMBER	Converts the input string to a number. `noop` if input is a number; `Undefined` if string doesn't represent a number.
IS_ARRAY	Returns a Boolean value indicating if the type of the specified expression is an array.
IS_BOOL	Returns a Boolean value indicating if the type of the specified expression is a Boolean.
IS_DEFINED	Returns a Boolean indicating if the property has been assigned a value. This function is supported only when the value is a primitive type. Primitive types include string, Boolean, numeric, or `null`. DateTime, object types and arrays aren't supported.
IS_NULL	Returns a Boolean value indicating if the type of the specified expression is null.
IS_NUMBER	Returns a Boolean value indicating if the type of the specified expression is a number.
IS_OBJECT	Returns a Boolean value indicating if the type of the specified expression is a JSON object.
IS_PRIMITIVE	Returns a Boolean value indicating if the type of the specified expression is a primitive (string, Boolean, numeric, or `null`).
IS_STRING	Returns a Boolean value indicating if the type of the specified expression is a string.

In routes conditions, the following string functions are supported:

Function	Description
CONCAT(x, y, …)	Returns a string that is the result of concatenating two or more string values.
LENGTH(x)	Returns the number of characters of the specified string expression.
LOWER(x)	Returns a string expression after converting uppercase character data to lowercase.
UPPER(x)	Returns a string expression after converting lowercase character data to uppercase.
SUBSTRING(string, start [, length])	Returns part of a string expression starting at the specified character zero-based position and continues to the specified length, or to the end of the string.
INDEX_OF(string, fragment)	Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string isn't found.
STARTSWITH(x, y)	Returns a Boolean indicating whether the first string expression starts with the second.
ENDSWITH(x, y)	Returns a Boolean indicating whether the first string expression ends with the second.
CONTAINS(x,y)	Returns a Boolean indicating whether the first string expression contains the second.

Query examples with the service SDKs

C# example

The query functionality is exposed by the C# service SDK in the RegistryManager class.

Here's an example of a simple query:

var query = registryManager.CreateQuery("SELECT * FROM devices", 100);
while (query.HasMoreResults)
{
    var page = await query.GetNextAsTwinAsync();
    foreach (var twin in page)
    {
        // do work on twin object
    }
}

The query object is instantiated with the parameters mentioned in the query results pagination section. Multiple pages are retrieved by calling the GetNextAsTwinAsync methods multiple times.

Node.js example

The query functionality is exposed by the Azure IoT service SDK for Node.js in the Registry object.

Here's an example of a simple query:

var query = registry.createQuery('SELECT * FROM devices', 100);
var onResults = function(err, results) {
    if (err) {
        console.error('Failed to fetch the results: ' + err.message);
    } else {
        // Do something with the results
        results.forEach(function(twin) {
            console.log(twin.deviceId);
        });

        if (query.hasMoreResults) {
            query.nextAsTwin(onResults);
        }
    }
};
query.nextAsTwin(onResults);

The query object is instantiated with the parameters mentioned in the query results pagination section. Multiple pages are retrieved by calling the nextAsTwin method multiple times.

Next steps

Learn about routing messages based on message properties or message body with the IoT Hub message routing query syntax.
Get specific examples of Queries for device and module twins or Queries for jobs.

Κοινή χρήση μέσω

IoT Hub query language for device and module twins, jobs, and message routing

Run IoT Hub queries

Basics of an IoT Hub query

SELECT clause

FROM clause

WHERE clause

GROUP BY clause

Expressions and conditions

Operators

Functions

Query examples with the service SDKs

C# example

Node.js example

Next steps

Σχόλια

Πρόσθετοι πόροι