AWS IoT mqtt json message issue on v3 (The Things Stack Cloud)

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"
  }
}

If you have a support plan, then you can also reach out to the support team for help.

No, this is correct. Your messages can be received by multiple gateways, so there can be rx metadata from multiple gateways in there.

I don’t have experience with AWS IoT, so I don’t know how you should query for that data.