locked
Access a array of an array RRS feed

  • Question

  • Hello!

    Im doing some tests with Stream Analytics and IoT Hub where I want to access som values from a JSON string. The challange is that the JSON string contain an array within an array. And I want values from both "levels".

    My data looks like this:

    {
      "messageType": "SubscribeData",
      "timeSent": 1485517547407,
      "list": [
        {
          "resourceType": "SampleList",
          "dataSourceAddress": {
            "resourceType": "DataSourceAddress",
            "did": "EUI64-0080E1030001697B-4-Energy",
            "locationId": "282923",
            "serverDid": "EUI64-0090DAFFFF0053A5",
            "variableName": {
              "resourceType": "VariableName",
              "name": "totalpower"
            },
            "instanceNumber": 0
          },
          "list": [
            {
              "resourceType": "SampleElectricalEnergySimple",
              "sampleTime": 1485517547266,
              "totalEnergy": 78365141091,
              "instantPower": 0
            }
          ]
        }
      ]
    }

    I want to get the did from the first array and the resourcetype and totalenergy from the second array.

    I've manages go the the did-attribute by using cross apply. But how do I go from there to do another cross apply?

    select a.messagetype, flat.arrayvalue.resourceType as rt
    , flat.arrayvalue.dataSourceAddress.did as da
    , flat.arrayvalue
    from testinput a
    cross apply getelements(a.list) as flat

    Anyone that knows how to do this?

    Regards,
    Alfred

    Friday, January 27, 2017 3:30 PM

Answers

  • You can do it like this:

    WITH flat1
    AS (
    	SELECT     a.messagetype
    		,flat.arrayvalue.resourceType AS rt    
    		,flat.arrayvalue.dataSourceAddress.did AS da   
    		,flat.arrayvalue.listFROM testinput aCROSS
    	APPLY getelements(a.list) AS flat
    	)
    SELECT     flat1.messagetype
    	,flat1.da
    	,flat1.rt
    	,    flat2.arrayvalue.totalEnergy    FROMflat1
    CROSS APPLY getelements(flat1.list) AS flat2
    

    • Marked as answer by Haiiko Monday, January 30, 2017 8:08 AM
    Friday, January 27, 2017 5:01 PM

All replies

  • You can do it like this:

    WITH flat1
    AS (
    	SELECT     a.messagetype
    		,flat.arrayvalue.resourceType AS rt    
    		,flat.arrayvalue.dataSourceAddress.did AS da   
    		,flat.arrayvalue.listFROM testinput aCROSS
    	APPLY getelements(a.list) AS flat
    	)
    SELECT     flat1.messagetype
    	,flat1.da
    	,flat1.rt
    	,    flat2.arrayvalue.totalEnergy    FROMflat1
    CROSS APPLY getelements(flat1.list) AS flat2
    

    • Marked as answer by Haiiko Monday, January 30, 2017 8:08 AM
    Friday, January 27, 2017 5:01 PM
  • Thank you so much!

    I did several tries with a second cross apply using a temp table like this, but I didn't get the referencing correct. Now it works like a charm.

    Regards,
    Alfred

    Monday, January 30, 2017 8:11 AM