Azure Stream Analytics join data from an Array to same Output

Question

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:

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:

Answer 1

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

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

---

• Please don't forget to click on or upvote 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