Queries devices by executing an SQL-like statement. This operation returns the devices that meet the conditions that you specified in the SQL-like statement.

Description

  • You can query devices on Enterprise Edition instances only in the China (Shanghai) and Japan (Tokyo) regions.
  • The QueryDeviceBySQL operation can return up to 10,000 devices each time you call the operation. For more information, see the "Syntax of LIMIT clauses" section of this topic.

QPS limits

You can call this API operation up to 10 times per second per account.

Note The RAM users of an Alibaba Cloud account share the quota of the account.

Debugging

OpenAPI Explorer automatically calculates the signature value. For your convenience, we recommend that you call this operation in OpenAPI Explorer. OpenAPI Explorer dynamically generates the sample code of the operation for different SDKs.

Request parameters

ParameterTypeRequiredExampleDescription
ActionStringYesQueryDeviceBySQL

The operation that you want to perform. Set the value to QueryDeviceBySQL.

SQLStringYesSELECT * FROM device where product_key = "a1*********" limit 100, 20

The SQL-like statement that you want to execute to query devices. For more information about specific requirements and examples, see the following section.

IotInstanceIdStringNoiot-cn-0pp1n8t****

The ID of the instance. You can view the ID of the instance on the Instance Overview page in the IoT Platform console.

Important
  • If your instance has an ID, you must specify the ID for this parameter. Otherwise, the call fails.
  • If no Overview page or ID is generated for your instance, you do not need to configure this parameter.

For more information, see Overview.

If you want to call the QueryDeviceBySQL operation to query devices, you must specify an SQL-like statement. The SQL-like statement must contain a SELECT clause and a WHERE clause. The statement can also contain an ORDER BY clause and a LIMIT clause. Each statement cannot exceed 400 characters in length.

Examples:

SELECT * FROM device WHERE product_key = "a1*********" order by active_time limit 0,10

SQL clause

Description

SELECT clause

SELECT [field]/[count(*)] FROM device

The field parameter specifies the fields that you want to obtain. The following table describes the fields. If you want to obtain all fields, specify an asterisk (*).

If you want to obtain the number of rows that match the specified conditions, specify count(*).

WHERE clause

WHERE [condition1] AND [condition2]

You can specify up to five conditions. Nesting is not supported. The following table describes the fields and operators.

You can use the AND or OR logical operator to connect conditions. You can use up to five logical operators.

ORDER BY clause (optional)

The ORDER BY clause is used to sort fields. The following fields can be sorted: gmt_create, gmt_modified, and active_time.

This clause is optional. If you do not specify this clause, the results are randomly sorted.

LIMIT clause (optional)

The LIMIT clause specifies the maximum number of rows to return on each page and the total number of rows to return. For more information, see the "Syntax of LIMIT clauses" section of this topic.

If you do not specify a LIMIT cause, limit 20 is specified by default.

Syntax of LIMIT clauses

Syntax

Description

limit k

The value of k must be less than or equal to 50, which specifies that the number of rows to return on each page cannot exceed 50. Examples:

SELECT * FROM device WHERE product_key = "a1*****" limit 10

limit n,k

The sum of the values of n and k must be less than or equal to 10,000 and the value of k must be less than or equal to 50. This specifies that the total number of rows to return cannot exceed 10,000 and the maximum number of rows to return on each page cannot exceed 50. Examples:

SELECT * FROM device WHERE product_key = "a1*****" limit 40,10
Fields

Field

Type

Description

product_key

text

The ProductKey of the product to which the device belongs.

iot_id

text

The ID of the device. By default, iot_id is returned.

name

text

The name of the device.

active_time

date

The time when the device was activated. The time is in the yyyy-MM-dd HH:mm:ss.SSS format and is accurate to the millisecond.

nickname

text

The alias of the device.

gmt_create

date

The time when the device was created. The time is in the yyyy-MM-dd HH:mm:ss.SSS format and is accurate to the millisecond.

gmt_modified

date

The time when the device information was last updated. The time is in the yyyy-MM-dd HH:mm:ss.SSS format and is accurate to the millisecond.

status

text

The status of the device. Valid values:

ONLINE: The device is online.

OFFLINE: The device is offline.

UNACTIVE: The device is not activated.

DISABLE: The device is disabled.

group.group_id

text

The ID of the device group.

tag.tag_name

text

The tag key of the device.

tag.tag_value

text

The tag value of the device.

ota_module.name

text

The name of the over-the-air (OTA) module.

We recommend that you use this field together with the ota_module.version field to specify the OTA module corresponding to the current OTA version number of the device.

If you do not configure the ota_module.version field, you cannot query devices by OTA module name.

ota_module.version

text

The firmware version of the OTA module.

Operators

Operator

Supported data type

=

number, date, and text

!=

number, date, and text

>

number and date

<

number and date

LIKE

text

Description:

  • = and !=: If you use these operators, the values of the fields that you want to query can be null.
  • LIKE: If you use this operator, only prefix match is supported. The prefix must be at least four characters in length and cannot contain special characters, such as backslashes (\), forward slashes (/), ampersands (&), plus signs (+), hyphens (-), exclamation points (!), parentheses (), colons (:), tildes (~), braces {}, asterisks (*), and question marks (?). The prefix must end with a percent sign (%).

    Example: SELECT * FROM device where product_key = "a1*********" and name LIKE "test%" limit 10.

In addition to the preceding operation-specific request parameters, you must configure common request parameters when you call this operation. For more information about common request parameters, see Common parameters.

Response parameters

ParameterTypeExampleDescription
CodeStringiot.system.SystemException

The error code returned if the call fails. For more information, see Error codes.

DataArray of SimpleDeviceSearchInfo

The device information returned if the call is successful.

ActiveTimeString2020-04-04 16:38:18.607

The time when the device was activated. The time is in the GMT format.

DeviceNameStringlight

The name of the device.

GmtCreateString2020-04-04 16:38:17.000

The time when the device was created. The time is in the GMT format.

GmtModifiedString2020-04-04 16:38:19.000

The time when the device information was last updated. The time is in the GMT format.

GroupsArray of SimpleDeviceGroupInfo

The information about the groups to which the device belongs.

GroupIdStringa1d21d2fas

The ID of the group.

IotIdStringQ7uOhVRdZRRlDnTLv****00100

The ID of the device. The ID is a unique identifier that is issued by IoT Platform to the device.

NicknameStringSmart light

The alias of the device.

OTAModulesArray of OTAModuleInfo

The information about the firmware of each device module.

FirmwareVersionStringa1-dads2-dad2

The version number of each OTA module.

ModuleNameStringSomeSampleModule

The name of the OTA module.

ProductKeyStringa1BwAGV****

The ProductKey of the product to which the device belongs.

StatusStringONLINE

The status of the device. Valid values:

  • ONLINE: The device is online.
  • OFFLINE: The device is offline.
  • UNACTIVE: The device is not activated.
  • DISABLE: The device is disabled.
TagsArray of TagInfo

The information about device tags.

TagNameStringColor

The tag key.

TagValueStringRed

The tag value.

ErrorMessageStringA system exception occurred.

The error message returned if the call fails.

RequestIdStringE55E50B7-40EE-4B6B-8BBE-D3ED55CCF565

The ID of the request.

TotalCountLong100

If you specify SELECT count(*) FROM device in the SQL-like statement, the number of rows that match the specified conditions is returned.

SuccessBooleantrue

Indicates whether the call was successful. Valid values:

  • true: The call was successful.
  • false: The call failed.

Examples

Sample requests

https://iot.cn-shanghai.aliyuncs.com/?Action=QueryDeviceBySQL
&IotInstanceId=iot-cn-0pp1n8t****
&SQL=SELECT * FROM device where product_key = "a1*********" limit 100, 20
&<Common request parameters>

Sample success responses

XML format

<QueryDeviceBySQLResponse>
  <RequestId>501CFABA-2C48-468D-B88C-3AA8E3B3A8F3</RequestId>
  <Data>
        <Status>OFFLINE</Status>
        <IotId>ii1*******</IotId>
        <GmtCreate>2020-04-04 16:38:17.000</GmtCreate>
        <ActiveTime>2020-04-04 16:38:18.607</ActiveTime>
        <GmtModified>2020-04-04 16:38:19.000</GmtModified>
        <ProductKey>a1*********</ProductKey>
        <DeviceName>testDevcieae7f3a</DeviceName>
  </Data>
  <Data>
        <Status>UNACTIVE</Status>
        <IotId>5wt*******</IotId>
        <GmtCreate>2020-04-04 16:37:32.000</GmtCreate>
        <Groups>
              <GroupId>Ix4*******</GroupId>
        </Groups>
        <Groups>
              <GroupId>Xrn*******</GroupId>
        </Groups>
        <Groups>
              <GroupId>J9l*******</GroupId>
        </Groups>
        <OTAModules>
              <ModuleName>SomeSampleModule</ModuleName>
              <FirmwareVersion>a1-dads2-dad2</FirmwareVersion>
        </OTAModules>
        <OTAModules>
              <ModuleName>SampleModule</ModuleName>
              <FirmwareVersion>a1-dads2-dad1</FirmwareVersion>
        </OTAModules>
        <GmtModified>2020-04-04 16:37:32.000</GmtModified>
        <ProductKey>a1*********</ProductKey>
        <DeviceName>testDevcie676a22</DeviceName>
  </Data>
  <Success>true</Success>
</QueryDeviceBySQLResponse>

JSON format

{
    "RequestId": "501CFABA-2C48-468D-B88C-3AA8E3B3A8F3",
    "Data": [
        {
            "Status": "OFFLINE",
            "IotId": "ii1*******",
            "GmtCreate": "2020-04-04 16:38:17.000",
            "ActiveTime": "2020-04-04 16:38:18.607",
            "GmtModified": "2020-04-04 16:38:19.000",
            "ProductKey": "a1*********",
            "DeviceName": "testDevcieae7f3a"
        },
        {
            "Status": "UNACTIVE",
            "IotId": "5wt*******",
            "GmtCreate": "2020-04-04 16:37:32.000",
            "Groups": [
                {
                    "GroupId": "Ix4*******"
                },
                {
                    "GroupId": "Xrn*******"
                },
                {
                    "GroupId": "J9l*******"
                }
            ],
            "OTAModules": [
                {
                    "ModuleName": "SomeSampleModule",
                    "FirmwareVersion": "a1-dads2-dad2"
                },
                {
                    "ModuleName": "SampleModule",
                    "FirmwareVersion": "a1-dads2-dad1"
                }
            ],
            "GmtModified": "2020-04-04 16:37:32.000",
            "ProductKey": "a1*********",
            "DeviceName": "testDevcie676a22"
        }
    ],
    "Success": true
}