Querying an IoT Hub Object with number key

Question

I am trying to retrieve information from a twin device of a third party IoT Hub instance.

The data I'm trying to access has the next format:

{
    "properties": {
        "reported": {
            "softwareLoad": {
                "0": {
                    "systemSWVer": "1.2.0",
                    "picSWVer": "0.0.42",
                    "bootStatus": "inactive",
                    "partitionId": 0
                },
                "1": {
                    "systemSWVer": "1.2.0",
                    "picSWVer": "0.0.42",
                    "bootStatus": "active",
                    "partitionId": 1
               },
               "beamTableCRC": "0x5454"
            }
        }
    }
}

I was trying to reach the variable systemSWVer to use it as a where clause, but each time I try to access I had an error retrieving the information in 0 and IoT Hub returns an error of a Bad Request.

I tried with this query

SELECT properties.reported.softwareLoad FROM devices WHERE properties.reported.softwareLoad.0.systemSWVer in ["1.2.0", "2.1.0"]

Is there a way to use it as a where clause in my query?

Note: I don't have access to the resource to change the format of the information.

Answer 1

I tried to recreate this case and found the same result. Always an error when you add a number in the expression. I also found a workaround; instead of using 0 or 1, you can add them as an escaped Unicode character. It's important to add them as an ASCII Unicode character, though. 0 becomes \u0030 and 1 becomes \u0031.

Try this query:

SELECT properties.reported.softwareLoad FROM devices 
WHERE properties.reported.softwareLoad.\u0030.systemSWVer in ['1.2.0', '2.1.0']

Please note: in any case, you need to use single quotes.

Edit: in my excitement, I forgot to test if just escaping the integer would also work. It does.

SELECT properties.reported.softwareLoad FROM devices 
WHERE properties.reported.softwareLoad.\0.systemSWVer in ['1.2.0', '2.1.0']