SQL access 2d array element

Question

Hi
I have this payload and want to do some comparison operation in aws iot rule:

```
{
"state":
  {
    "reported":{
        "temp": [
                 [
                 1,
                0,
                2,
                "hello world",
                "-",
               "",
               "",
              0
               ] ,
              []
             ]
           }
     }
}
```

. I want to compare if temp[0][2] is equal to 2 or not.
I have written this sql statement 
```
SELECT * FROM '$aws/topic' WHERE state.reported.temp[0][2] = 2 
```
but this throws following exception

```
SqlParseException
Expected a comparison operation: JPathNode(List(IdentNode(state), IdentNode(reported), IdentNode(temp))) state.reported.temp[0][2] =2 -------------------------------------------------^ at 1:50
```
So anyone up for help?
Thanks

Accepted Answer

To access elements in a collection (array, string, object ) use the SQL function [get](https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html) function:

In your case, use get() instead of square brackets to access your array element:
```
SELECT * as result FROM 'topic' where get(get(state.reported.temp,0),2) = 2
```

SQL access 2d array element

相關內容