locked
Output complete jsonstring to azure database RRS feed

  • Question

  • Hi,

    Im sending some sensordata to an IoT-hub using a websocket (node js)

    In stream analytics I then want to use this data in two different ways. One is to output it to power bi for live updates on motion sensors. The other output I want is to an Azure database (2016) and to store it in its raw format wich is json.

    My output to power bi work perfectly, but the ASA job that savs to the database does not.

    My qyery looks like this:

    SELECT
        flat.arrayvalue.dataSourceAddress.did as SENSOR_ID
        , a.list as RAWDATA 
    INTO mydb
    FROM iothub as a
    CROSS APPLY getelements(a.list) as flat

    incomming message example:

    {
      "messageType": "SubscribeData",
      "timeSent": 1486462741265,
      "list": [
        {
          "resourceType": "SampleList",
          "dataSourceAddress": {
            "resourceType": "DataSourceAddress",
            "did": "EUI64-0080E103000238F2-4-Motion",
            "locationId": "282923",
            "serverDid": "EUI64-0090DAFFFF0053A5",
            "variableName": {
              "resourceType": "VariableName",
              "name": "motion"
            },
            "instanceNumber": 0
          },
          "list": [
            {
              "resourceType": "SampleMotion",
              "sampleTime": 1486462014732,
              "value": 87,
              "timeLastMotion": 1486462014732
            }
          ]
        }
      ]
    }

    The data ends up in my table with the sensor_id in an own column, but the column RAWDATA (nvarcharmax) only contains the word "Array". Not the actual json string.

    Anyone that knows what Im doing wrong?

    Regards,
    Alfred


    • Edited by Haiiko Thursday, February 9, 2017 11:11 AM
    Thursday, February 9, 2017 11:09 AM

Answers

  • After even more searching I found the solution. There is no built in support for doing this, instead I hade to write a user-defined function.

    function main(jsonstring) {
        return JSON.stringify(jsonstring);
    }

    And then I use that in my query:

    SELECT
        flat.arrayvalue.dataSourceAddress.did as SENSOR_ID
        , udf.json2string(a) as RAWDATA
    INTO mydb
    FROM iothub as a
    CROSS APPLY getelements(a.list) as flat

    Works lika a charm!

    Regards,
    Alfred

    • Marked as answer by Haiiko Thursday, February 9, 2017 1:25 PM
    Thursday, February 9, 2017 1:24 PM