Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/azure/iot-hub/iot-hub-devguide-query-language below:

Understand the Azure IoT Hub query language

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 more information about query language for message routing, see IoT Hub message routing query syntax.

For specific examples, see Queries for IoT Hub device and module twins or Queries for IoT Hub 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 and size for your solution.

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 Hub SDKs.

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>]

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.

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
  

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.

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, as shown in this example: 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.

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.

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:

The following operators are supported:

When querying twins and jobs the only supported function is:

In routes conditions, the following math functions are supported:

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

In routes conditions, the following string functions are supported:

The query functionality is exposed by the Azure IoT Hub service SDK for .NET 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.

The query functionality is exposed by the Azure IoT Hub 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.

• 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 IoT Hub device and module twins or Queries for IoT Hub jobs.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4