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.
Debugging
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 execute 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 the instance on the Instance Overview page in the IoT Platform console. Important
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 |
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. |
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
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 is 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 name 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 the 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 status of the device. Valid values:
|
Tags | Array of TagInfo | The information about device tags. | |
TagName | String | Color | The tag key. |
TagValue | String | Red | The tag value. |
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 |
Success | Boolean | true | Indicates whether the call was successful. Valid values:
|
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
}