Azure Stream Analytics join data from an Array to same Output

Ørjan Vatsøy 1 Reputation point
2022-04-11T13:23:22.51+00:00

Hi,

I have an JSON string looking like this:

  {  
    "gatewayData": [  
      {  
        "tag_id": "ra-ftld://driver-ftld/RNA://$Global/SeaGreen_IOT::[B24_Main_PLC]B24_50FT050_S.Out",  
        "model_id": "B24MainPLC.SeaWater.UDT_Test.B24_50FT050_S.Out",  
        "vqts": [  
          {  
            "v": 56.77355194091797,  
            "q": 192,  
            "t": "2022-04-11T07:25:25.054Z"  
          }  
        ],  
        "mimeType": "x-ra/cip/real"  
      },  
      {  
        "tag_id": "ra-ftld://driver-ftld/RNA://$Global/SeaGreen_IOT::[B24_Main_PLC]B24_50FT050_S.In",  
        "model_id": "B24MainPLC.SeaWater.UDT_Test.B24_50FT050_S.In",  
        "vqts": [  
          {  
            "v": 6997,  
            "q": 192,  
            "t": "2022-04-11T07:25:25.054Z"  
          }  
        ],  
        "mimeType": "x-ra/cip/real"  
      }  
    ],  
"EventProcessedUtcTime": "2022-04-13T06:35:13.5935063Z",  
"PartitionId": 1,  
"EventEnqueuedUtcTime": "2022-04-13T06:26:12.1360000Z",  
"IoTHub": {  
  "MessageId": "cgp-iot-0.16754946715585906",  
  "CorrelationId": null,  
  "ConnectionDeviceId": "b24plc",  
  "ConnectionDeviceGenerationId": "637843086115275433",  
  "EnqueuedTime": "2022-04-13T06:26:12.0420000Z"  
  }  
 }  
  

The Array has several "model_id", and each "model_id" has an value ( "v") .
I have tried to use UNION to join the data like this:

WITH  
Input1_P AS (  
SELECT  
CustomerSensorValue.ArrayValue.model_id AS modelId_Out,  
SensorData.ArrayValue.v AS realOut_Out,  
NULL AS modelId_In,  
NULL AS realOut_In  
FROM tempsensor AS i PARTITION BY partitionId   
CROSS APPLY GetArrayElements(i.gatewayData) AS CustomerSensorValue  
CROSS APPLY GetArrayElements(CustomerSensorValue.ArrayValue.vqts) AS SensorData  
WHERE CustomerSensorValue.ArrayValue.model_id LIKE '%Out%'  
),  
  
Input2_P AS (  
SELECT  
NULL AS modelId_Out,  
NULL AS realOut_Out,  
CustomerSensorValue.ArrayValue.model_id AS modelId_In,  
SensorData.ArrayValue.v AS realOut_In  
FROM tempsensor AS i PARTITION BY partitionId    
CROSS APPLY GetArrayElements(i.gatewayData) AS CustomerSensorValue  
CROSS APPLY GetArrayElements(CustomerSensorValue.ArrayValue.vqts) AS SensorData  
WHERE CustomerSensorValue.ArrayValue.model_id LIKE '%In%'  
),  
  
Outgoing AS (  
SELECT modelId_Out, realOut_Out, modelId_In, realOut_In  
FROM Input1_P   
UNION  
SELECT modelId_Out, realOut_Out, modelId_In, realOut_In  
FROM Input2_P   
)  
  
SELECT *  
FROM Outgoing  
  

But the I am not getting the result I am looking for:
191942-image.png

What is wrong in my code ? And I guess is it a easier way to obtain this.
Now I only have 2 tag, sow I think this will be a bit hard if I get 15 tags
I am looking for an result like:
195198-image.png

Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
339 questions
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2022-05-03T21:22:21.69+00:00

    Hello @Ørjan Vatsøy ,
    Thanks for the question and using MS Q&A platform.

    As we understand the ask here is to get the output in the format called out here , please do let us know if its not accurate.
    At this time I see that you are using an UNION and so you are getting the NULL which I is not ehat you need . I do not have the fill data , nut I think You can use a JOIN . The key is to get the modelID on which we can join . On looking the model name it looked like the last 2 or three characters where different , so i used to substring function to get the actual name of the model

    For out

    substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 3 )) as ModelNo_withOut_out

    for in

    substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 2 )) as ModelNo_withOut_In

    the below Query worked for me

    WITH
    Input1_P AS ( SELECT
    CustomerSensorValue.ArrayValue.model_id AS modelId_Out,
    substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 3 )) as ModelNo_withOut_out ,
    SensorData.ArrayValue.v AS realOut_Out
    FROM json AS i PARTITION BY partitionId
    CROSS APPLY GetArrayElements(i.gatewayData) AS CustomerSensorValue
    CROSS APPLY GetArrayElements(CustomerSensorValue.ArrayValue.vqts) AS SensorData
    WHERE CustomerSensorValue.ArrayValue.model_id LIKE '%Out%'
    ) ,
    Input2_P AS (
    SELECT
    CustomerSensorValue.ArrayValue.model_id AS modelId_In,
    substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 2 )) as ModelNo_withOut_In ,
    SensorData.ArrayValue.v AS realOut_In
    FROM json AS i PARTITION BY partitionId
    CROSS APPLY GetArrayElements(i.gatewayData) AS CustomerSensorValue
    CROSS APPLY GetArrayElements(CustomerSensorValue.ArrayValue.vqts) AS SensorData
    WHERE CustomerSensorValue.ArrayValue.model_id LIKE '%In%'
    ) ,
    Outgoing AS (
    SELECT Input1_P.realOut_Out, Input1_P.modelId_Out
    ,Input2_P.modelId_In,Input2_P.realOut_In
    FROM Input1_P
    JOIN Input2_P
    On Input1_P.ModelNo_withOut_out = Input2_P.ModelNo_withOut_In
    AND DATEDIFF(minute,Input1_P,Input2_P) BETWEEN 0 AND 15

    )

    SELECT *
    FROM Outgoing

    198612-image.png

    Please do let me if you have any queries.
    Thanks
    Himanshu


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
      • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments