question

rjanVatsy-8872 avatar image
0 Votes"
rjanVatsy-8872 asked HimanshuSinha-MSFT commented

Azure Stream Analytics join data from an Array to same Output

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
image.png (9.9 KiB)
image.png (1.9 KiB)
image.png (3.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @rjanVatsy-8872,
Thanks for the question and using MS Q&A platform.

The JSON which you have shared is malformed , can you please share the correct JSON ? Once we have the JSON , we can try to play around with that . Also can you share what is the desired output you are looking for for ?

We will wait to hear from you . .
Thanks
Himanshu

1 Vote 1 ·
rjanVatsy-8872 avatar image rjanVatsy-8872 HimanshuSinha-MSFT ·

Updated :)

0 Votes 0 ·

Hello @rjanVatsy-8872,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered

Hello @rjanVatsy-8872,
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



image.png (7.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.