Search code examples
sqlazureazure-stream-analytics

How to concat all the results of a query in Stream Analytics Query?

I have an input from IoT Hub and reference input from Blob Storage. I want to concat all the results that I found when I match for IoTHubInput.id with BlobStorageInput.id.

Currently the query looks like this:

   SELECT Blob
   FROM iothub IoTHub
   JOIN blob Blob
   ON IoTHub.id = Blob.deviceId

The result of this query looks like this:

[
  {
    "blob": 
    {
        "deviceId": "test001",
        "data": "Sample1"
    }
  },
  {
    "blob": 
    {
        "deviceId": "test002",
        "data": "Sample2"
    }
  },
  {
    "blob": 
    {
        "deviceId": "test003",
        "data": "Sample3"
    }
  },
]

It will return 3 message to my output, but I don't want that.

I want all the data in the blob if the id is match, but I actually want them all CONCAT and become an array. For example:

[
    {
        "deviceId": "test001",
        "data": "Sample1"
    },
    {
        "deviceId": "test002",
        "data": "Sample2"
    },
    {
        "deviceId": "test003",
        "data": "Sample3"
    }
]

I searched around the internet and found GROUP CONCAT however Stream Analytics does not support that. Is there any other alternative workaround that I can do that will achieve the same?

Solution

  • I reproduce your test data:

    enter image description here

    Per my knowledge,there is no GROUP CONCAT feature in the ASA. Actually,the output of ASA is an array already.You could verify it in the download result:

    enter image description here

    I tested withe a Blob output and it shows as array. If your output produces 3 rows ,I suggest you using Azure Function Output to accept the array first then do next transfer steps.