Querying Iothub device twin tags using string methods

Question

I wants to get list of devices which starts with 'test' word. Or something like, get all devices where tags.email starts with 'test'. I have gone through documentation, but as per my findings it only supports querying with exact match and also supports few operators like (>, <, !=, <> etc..).

Can anyone help me with this? I want some query like,

SELECT * from devices WHERE deviceId LIKE 'test%';

or

SELECT * from devices WHERE STARTS_WITH(tags.email, 'test')

Answer 1

The doc described in details set of the like SQL statements for querying devices in the IoT Hub. The requested queries are not currently supported in the IoT Hub, however you have some options to handle your requirements:

1. add the tags property for each device, for example:

   "tags": { "environment": "test" }
   

   and based on the value of the tags.environment you can make query on the IoT Hub side, for examples:

   SELECT deviceId FROM devices  WHERE tags.environment IN ['test', 'dev']
   
   SELECT deviceId FROM devices  WHERE tags.environment NIN ['test']
   

2. this option is based on the pre-querying devices from the IoT Hub and then to use a LINQ query to obtain a final result of the query, for example based on your case:

    SELECT deviceId, tags FROM devices
   

also you can use the WHERE statement to minimize set of the devices in the pre-query step.

Once you have this pre-query result, you can use LINQ rich statements for selecting devices based on the deviceId and/or tags properties.

Using the option #2, the following example can be used:

SELECT deviceId, tags.email FROM devices WHERE is_defined(tags.email)

and then based on the LINQ you can finalized your query on the client side.