IOT core how to get value from array payload?

Question

I get a payload from a BLE IOT Gateway with multiple BLE devices connected which looks like this:
```
[
  {
    "timestamp": "2023-04-19T20:44:26.344Z",
    "type": "Gateway",
    "mac": "AC233FC0D394",
    "gatewayFree": 21,
    "gatewayLoad": 0.33000000000000002
  },
  {
    "timestamp": "2023-04-19T20:44:26.343Z",
    "type": "S4",
    "mac": "AC233FAC728A",
    "bleName": "",
    "rssi": -62,
    "battery": 100,
    "unlocked": false,
    "uninstalled": false,
    "triggered": false
  },
  {
    "timestamp": "2023-04-19T20:44:26.491Z",
    "type": "V3Info",
    "mac": "AC233FAA8734",
    "bleName": "",
    "rssi": -36,
    "battery": 100,
    "version": "v3.0.7"
  },
  {
    "timestamp": "2023-04-19T20:44:26.948Z",
    "type": "V3S3",
    "mac": "AC233FAA8734",
    "bleName": "S3",
    "rssi": -48,
    "temperature": 19.18,
    "humidity": 50.850000000000001,
    "screenStatus": 0,
    "sensorStatus": 0,
    "notHumidity": 0
  }
]
```

How do I query this. For example what if I want to query for type = s4 and unlocked = false
Thanks!

Answer

Hi Sanderk77. The documentation on nested object queries should get you on the right path: https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-nested-queries.html

UPDATE: Example query (using a wildcard to deal with the un-named array).

```
SELECT (SELECT * FROM * where type = 'S4' and unlocked = false) as value FROM 'topic'
```

IOT core how to get value from array payload?

Relevant content