Transcoding arrays in Azure Stream Analytics

Question

Let's say i have the following JSON data structure coming into Stream Analytics (generated by an IoT device):

{
    "user":"bob",
    "messages":[
    {
        "mac":"AA:BB:CC:DD:EE:FF",
        "data":[
            {
                "messagetype1":{
                    "param1":83,
                    "param2":82
                },
                "messagetype2":{
                    "param3":83,
                    "param4":82
                }
            }
        ]
    }]
}

Above you can see that the data array contains named objects. I need to store the object name as well as the other parameters. Is there a built-in function to access the object name of an array element? messagetype1 and messagetype2 are just examples.

E.g.I would like to store:

{ "user":"bob", "mac":"AA:BB:CC:DD:EE:FF", "type":"messagetype1", "param1":83}
{ "user":"bob", "mac":"AA:BB:CC:DD:EE:FF", "type":"messagetype1", "param2":82}
{ "user":"bob", "mac":"AA:BB:CC:DD:EE:FF", "type":"messagetype2", "param3":83}
{ "user":"bob", "mac":"AA:BB:CC:DD:EE:FF", "type":"messagetype2", "param4":82}

Answer 1

The best way to deal with this is probably use JavaScript UDFs.

Though one can usually get away with some combination of Array and Record functions tied up via CROSS APPLY, in your case the complication is with unwrapping different messagetype* records. You will need to know the set of all possible param* names to craft correct query. Alternatively you can use GetRecordProperties, but then instead of desired { ... "param1":83} structure you will end up with {... "field_name":"param1", "field_value":83} structure.