AWS IoT Core for Lambda event missing data

Question

I have a TEKTELIC smart room sensor connected to AWS IoT Core for Lambda. The destination publishes to a topic. In the MQTT test client I get a nicely formed message:

{
  "WirelessDeviceId": "24e8d6e2-88c8-4057-a60f-66c5f3ef354e",
  "PayloadData": "A2cA4ARoaAD/ASw=",
  "WirelessMetadata": {
    "LoRaWAN": {
      "ADR": true,
      "Bandwidth": 125,
      "ClassB": false,
      "CodeRate": "4/5",
      "DataRate": "3",
      "DevAddr": "019e3fcb",
      "DevEui": "647fda00000089e2",
      "FCnt": 4676,
      "FOptLen": 0,
      "FPort": 10,
      "Frequency": "904700000",
      "Gateways": [
        {
          "GatewayEui": "647fdafffe014abc",
          "Rssi": -92,
          "Snr": 5.800000190734863
        },
        {
          "GatewayEui": "0080000000024245",
          "Rssi": -93,
          "Snr": 7.25
        },
        {
          "GatewayEui": "24e124fffef464da",
          "Rssi": -86,
          "Snr": 4.25
        }
      ],
      "MIC": "eb050f05",
      "MType": "UnconfirmedDataUp",
      "Major": "LoRaWANR1",
      "Modulation": "LORA",
      "PolarizationInversion": false,
      "SpreadingFactor": 7,
      "Timestamp": "2022-12-07T21:46:13Z"
    }
  }
}

when I subscribe to the topic with a lambda:

Rule query statement: SELECT *, topic() AS topic FROM 'lora/#'

I am missing most of the data:

{
    "Gateways": {
        "Timestamp": "2022-12-07T21:46:13Z",
        "SpreadingFactor": 7,
        "PolarizationInversion": false,
        "Modulation": "LORA",
        "Major": "LoRaWANR1",
        "MType": "UnconfirmedDataUp",
        "MIC": "eb050f05",
        "Snr": 4.25,
        "Rssi": -86,
        "GatewayEui": "24e124fffef464da"
    },
    "Snr": 7.25,
    "Rssi": -93,
    "GatewayEui": "0080000000024245",
    "topic": "lora/tektelic/smart_room"
}

The relevant code is:

def handler(event, context):
    print(json.dumps(event))

The event looks like approximately half the data, malformed and in reverse order. There is a Gateways [ ] in the original event, it is now an object with some data from the original array, and other data that was outside the array.

The info on the device that sent the message, and the payload I want to process are missing.

I am following this solution construct pattern, the only modifications are the lambda code and select statement.

I tried increasing the memory form the default 128M to 1024M with no changes.

I am also storing the raw messages in AWS S-3, following this construct pattern, and it matches the MQTT data. I made similar changes to select statement in it.

Thoughts on where to look for issues?

Most recent insight is that the select statement:

iot_topic_rule_props=iot.CfnTopicRuleProps(
topic_rule_payload=iot.CfnTopicRule.TopicRulePayloadProperty(rule_disabled=False, description="Processing of DTC messages from Lora Sensors.", sql="SELECT topic() AS topic, * FROM 'lora/#'", actions=[])),

Replacing the sql with:

sql="SELECT * FROM 'lora/#'",

generates a nicely formed event.

Replacing it with:

sql="SELECT topic() AS topic, * FROM 'lora/#'",

generates the same malformed event, except topic is the first tag instead of the last. I'm going to leave this open for an answer on how what is going on, because it feels like a bug. This should generate an error if it's just unhappy with the sql.

Answer 1

The Key to making it work is to include the aws_iot_sql_version:

                sql="SELECT *, topic() AS topic FROM 'lora/#'",
                aws_iot_sql_version="2016-03-23",

according to the docs the default value is "2015-10-08", however the the console uses "2016-03-23". I have not done the research to see the details.