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

Usage notes

You can call this operation to query the devices in an IoT Platform instance that is of the Enterprise Edition and resides only in the China (Shanghai), China (Beijing), China (Shenzhen), or Japan (Tokyo) region.

Limit for QPS

Each Alibaba Cloud account can run up to 10 queries per second (QPS).

Note 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

Parameter Type Required Example Description
Action String Yes QueryDeviceBySQL

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

SQL String Yes SELECT * FROM device where product_key = "a1*********" limit 100, 20

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

IotInstanceId String No iot-cn-0pp1n8t****

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

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

For more information, see Instance overview.

When you 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.

Example:

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

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, you can 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.

You can leave this clause unspecified. If you do not specify this clause, the results are sorted in a random manner.

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. You can specify a LIMIT clause in one of the following types of syntax:

  • 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. Example: SELECT * FROM device WHERE product_key = "a1*****" limit 10.

  • limit n,k

    The sum of the value of n and the value of 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. Example: SELECT * FROM device WHERE product_key = "a1*****" limit 40,10.

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

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 DeviceName 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 value of the device tag.

ota_module.module_name

text

The name of the OTA module.

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

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

ota_module.firmware_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 4 characters and cannot contain any 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, see Common request parameters.

Response parameters

Parameter Type Example Description
Code String iot.system.SystemException

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

Data Array of SimpleDeviceSearchInfo

The device information returned if the call was successful.

ActiveTime String 2020-04-04 16:38:18.607

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

DeviceName String light

The DeviceName of the device.

GmtCreate String 2020-04-04 16:38:17.000

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

GmtModified String 2020-04-04 16:38:19.000

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

Groups Array of SimpleDeviceGroupInfo

The information about the groups to which the device belongs.

GroupId String a1d21d2fas

The ID of each group.

IotId String Q7uOhVRdZRRlDnTLv****00100

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

Nickname String Smart light

The alias of the device.

OTAModules Array of OTAModuleInfo

The information about the firmware of each device module.

FirmwareVersion String a1-dads2-dad2

The version number of each OTA module.

ModuleName String SomeSampleModule

The name of the OTA module.

ProductKey String a1BwAGV****

The ProductKey of the product to which the device belongs.

Status String ONLINE

The device status. Valid values: Valid values:

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

The information about device tags.

TagName String Color

The key of each tag.

TagValue String Red

The value of the tag.

ErrorMessage String A system exception occurred.

The error message returned if the call fails.

RequestId String E55E50B7-40EE-4B6B-8BBE-D3ED55CCF565

The ID of the request.

TotalCount Long 100

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

Success Boolean true

Indicates whether the call was successful.

  • 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
}

Error code

For a list of error codes, visit the API Error Center.