แก้ไข

แชร์ผ่าน

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

  • บทความ

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.

  1. Sign in to the Azure portal and navigate to your IoT hub.
  2. Select Queries from the Device management section of the navigation menu.
  3. 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