How to get IoT Analytics to create a new row for each element in the input JSON array?

Question

If I am receiving a data record at a time as a JSON array from an IoT device in my channel. The received message looks like this :

{ 
    "state":{ 
        "reported":{
            "temperature": 24.28,
            "humidity": 37.67,
            "pressure": 1019.57,
            "proximity": 1485
        }
    }
}

the datastore is:

{ 
    reported = { 
        temperature = 24.28,
        humidity = 37.67, 
        pressure = 1019.57, 
        proximity = 1485
    }
}

My desired result is :

temperature        humidity        pressure        proximity  
Value1             Value2          Value3          Value4
AnotherValue1      AnotherValue2   AnotherValue3   AnotherValue4

How can I get IoT Analytics to create a new row in the datastore for each element within the received JSON array?

Answer 1

In order to have separate columns for the temperature, humidity, pressure, and proximity attributes in your datastore you have the following options:

1. Modify the message directly using AddAttributes and RemoveAttributes activities in your pipeline.

   1. Add temperature, humidity, pressure, and proximity to the top level of the json via an AddAttributes activity.
   2. Remove state from the top level of the json via a RemoveAttributes activity.

2. Modify the message directly using an AWS Lambda activity to transform the json so that temperature, humidity, pressure, and proximity are top level keys of the json and state and reported are no longer keys.

If instead you want to maintain the current structure of the message in your datastore, but would like your query results to be in the format you specified, you can accomplish this via the following sample query.

SELECT state.reported.temperature, state.reported.humidity, state.reported.pressure, state.reported.proximity FROM datastore WHERE state IS NOT NULL AND state.reported IS NOT NULL

For more information about this query see SQL expressions in IoT Analytics.