I’m using the Paid version of the Things Stack Cloud with a successful AWS IoT integration. The MQTT json messages are coming through and I’m able to query these using the AWS SQL editor when creating a specific AWS IoT rule. I can access most parts of the JSON message except for the rx_metadata section. I believe this is because this section of the JSON message is formatted as an array.
For example, for the given JSON message below, I can access the payload information in SQL section of the rule as follows
SELECT uplink_message.decoded_payload.battery AS battery
However, since the rx_metadata element is wrapped as an array [....] I cannot access the elements in SQL as the following throws an error
uplink_message.rx_metadata[0].gateway_ids.gateway_id AS gateway_id,
Is it a bug that rx_metadata uses the [...] format in the JSON message, and if not, how do I access the gateway_id using SQL as in the above example?
Example JSON Message
"end_device_ids": {
"device_id": "xxxxxxx-xxxxxxx-temp",
"application_ids": {
"application_id": "xxxxxxx-application"
},
"dev_eui": "A84041F121829C96",
"join_eui": "A000000000000100",
"dev_addr": "260857BB"
},
"correlation_ids": [
"as:up:01EY3PFBNNN0A2GSPW89HMR9PC",
"gs:conn:01EY3D74K7P2GMHTYSPK5EGSGM",
"gs:up:host:01EY3D74KJMX68RVS27B7ABR8P",
"gs:uplink:01EY3PFBDRDB51HKG1PS24MEAY",
"ns:uplink:01EY3PFBEEJ834ZD3V77G41B0V",
"rpc:/ttn.lorawan.v3.GsNs/HandleUplink:01EY3PFBDTQYPDPFG0X4FY1YRF"
],
"received_at": "2021-02-09T15:27:35.094022187Z",
"uplink_message": {
"session_key_id": "AXeG2gutEQiA4+WB/YKA1g==",
"f_port": 2,
"f_cnt": 19,
"frm_payload": "zBsF+wGohAAAf/8=",
"decoded_payload": {
"battery": 3.099,
"event": "Interrupt Sensor send",
"humidity": 42.4,
"temperature": 15.31
},
"decoded_payload_warnings": [],
"rx_metadata": [
{
"gateway_ids": {
"gateway_id": "xxxxxxx-xxxxxxx-lg308-gateway",
"eui": "A840411EAF644150"
},
"time": "2021-02-09T15:27:33.578929Z",
"timestamp": 1178847803,
"rssi": -91,
"channel_rssi": -91,
"snr": 7.2,
"uplink_token": "Ci8KLQohZnJlc2hwZXQtc2tpYmJlcmVlbi1sZzMwOC1nYXRld2F5EgioQEEer2RBUBC7lI+yBBoMCObTioEGENrt85ADIPjst8annAI=",
"channel_index": 2
}
],
"settings": {
"data_rate": {
"lora": {
"bandwidth": 125000,
"spreading_factor": 7
}
},
"data_rate_index": 5,
"coding_rate": "4/5",
"frequency": "868500000",
"timestamp": 1178847803,
"time": "2021-02-09T15:27:33.578929Z"
},
"received_at": "2021-02-09T15:27:34.862598490Z",
"consumed_airtime": "0.061696s"
}
}