AWS IoT Rule SQL republish get thing name as JSON object key

0

I'm publishing Shadow updates from multiple iot devices. I want to filter those updates and extract only some data that will update another thing shadow (let's call it main shadow) with a list of Json objects. Each json object key is the thing id.

For that, I have a rule that is subscribed to $aws/things/+/shadow/update/documents to capture all the devices shadow updates. So for example for the following updates

to $aws/things/device101/shadow/update/documents

{
 state: {
    reported: {
      "temperature": "28"
    }
 }
}

to $aws/things/device202/shadow/update/documents

{
 state: {
    reported: {
      "temperature": "31"
    }
 }

the main shadow will result in

{
 state: {
    reported: {
      "device101": {
         "temperature": "28"
      }
      "device202": {
         "temperature": "31"
      }    
    }
 }

In my SQL I still can't find the way to use the thing id as a key in the json data that will be published to the main shadow.

I'm using topic(3) as the function to get the thing name and Literals to build the json but it keeps telling me that it expects a key. I tried to cast the topic(3) function but same result

SELECT { topic(3): {'temperature': current.state.reported.temperature }} as state.reported FROM '$aws/things/+/shadow/update/documents'

SqlParseException
Expected a key, but got IDENT(topic). Object literals should follow the format {"keyName": any-expression, "anotherKey": any-expression} topic(3): 

Any idea how this can be achieved ?

thanks

mvp
asked a year ago668 views
2 Answers
2

Thanks for the challenging post :) I really tried multiple ways to get this to work. But the only way I could achieve this was to use a lambda function to format this json.

The SQL expression would look like this:

SELECT aws_lambda("arn:aws:lambda:us-east-1:[account_id]:function:[name_of_function]", {"device":topic(1),"payload":state.reported}) as state FROM '$aws/things/+/shadow/update/documents'

The lambda function would do the following:

import json

def lambda_handler(event, context):
    return {
            'reported':{
                event['device']: event['payload']
            }
        }

Here is a link to how you can execute a lambda function within a query: https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html#iot-func-aws-lambda

Hope this helps!

profile pictureAWS
EXPERT
answered a year ago
  • many thanks for your answer. I did end it up transforming the payload with a lambda.

  • This works but you might face an issue with the shadow document size quota. While you can request an increase for the quota, every time you update the document, you will be charged for the whole shadow size in increments of 1kb. So even if you update few hundredths bytes in the shadow and your shadow is 6Kb in size, you would pay for 6x1Kb operations. Using named shadows (see my answer below) is more scalable and more cost effective.

1
Accepted Answer

Hi mvp,

what you are trying to do is not currently supported by the SQL language in AWS IoT Rules. Another issue with such approach is that the size of a shadow document is limited, hence, depending on the number of devices or properties each device reports, you will sooner or later hit such limit.

What you can do instead is to use Named Shadows in combination with Fleet Indexing. Instead of adding each individual device as part of the shadow document of the main shadow, you can add them as named shadows.

If you then use Fleet Indexing to search for the main shadow, you will also get all the name shadows associated with the main shadow in the response of a SearchIndex call. Something similar to the following, where the named shadows are included in the name property:

{
    "desired": {
        "welcome": "aws-iot"
    },
    "reported": {
        "welcome": "aws-iot"
    },
    "metadata": {
        "desired": {
            "welcome": {
                "timestamp": 1632389871
            }
        },
        "reported": {
            "welcome": {
                "timestamp": 1632389871
            }
        }
    },
    "hasDelta": false,
    "version": 15,
    "name": {
        "connection_info": {
            "reported": {
                "lastConnected": 1663695514109,
                "ipAddress": "172.31.33.131",
                "lastDisconnected": 1663695513922,
                "disconnectReason": "SERVER_INITIATED_DISCONNECT"
            },
            "metadata": {
                "reported": {
                    "lastConnected": 1663695514109,
                    "ipAddress": "172.31.33.131",
                    "lastDisconnected": 1663695513922,
                    "disconnectReason": "SERVER_INITIATED_DISCONNECT"
                }
            },
            "hasDelta": false,
            "version": 472
        }
    }
}
AWS
EXPERT
answered a year ago
  • thank you. also works...! :)

  • @MassimilianoAWS there a couple of things that are not clear for me.

    The documentation at Managing Fleet Indexing tells that "you can add up to 10 shadow names per AWS account". How can I manage hundred of things that for example each one has one classic and three named shadows?. In the AWS console you need to add each named shadow by name

    Using the Fleet Indexing, how can I run a query that returns the shadow(json) that you mentioned. Get the classic shadow and the named shadows given the SHADOW NAME. Basically how can I index the name of the named shadows to do a search?

    I need to be able to fetch in one single API call the classic and all the named shadows. I'm gonna have an SPA react web app that needs to see the status of all the things. and I'm thinking of doing this by having the react app subscribing (mqtt pbsub) to the shadow update topic to display the changes in the things shadow as soon as when they happen

    thanks

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions