Good day. The situation is the following, on the remote object there are sensors that send indicators to the IoT HUB on the Microsoft Azure platform using the MQTT protocol in the form of JSON. Then there is the Stream Analytics Job, which parses the JSON and passes it to the SQL Database and then the data is visualized.

The thing is, there are two types of sending JSON, one with stream values ​​in the array during an emergency on a remote object, and the other sends a single indicator every 30 minutes in the normal state.

There is the following query in Stream Analytics, for a JSON parsm that accepts streaming array input but ignores inputting a single metric without an array:

SELECT dateadd(ms, flat.arrayindex * input.LeakSampl, dateadd(second, cast(substring(input.DT, 16, 2) as bigint), dateadd(minute, cast(SUBSTRING(input.DT, 13, 2) as bigint), dateadd(hour, cast(SUBSTRING(input.DT, 10, 2)as bigint), dateadd(day, cast(SUBSTRING(input.DT, 1, 2)as bigint) -1, dateadd(month, cast(SUBSTRING(input.DT, 4, 2)as bigint) - 1, dateadd(year, cast(SUBSTRING(input.DT, 7, 2)as bigint), cast('2000-01-01T00:00:00' as datetime)))))))) as DT, cast(input.EventEnqueuedUtcTime as datetime) as EventEnqueuedUtcTime, cast(input.EventProcessedUtcTime as datetime) as EventProcessedUtcTime, input.IoTHub.ConnectionDeviceId as object_name, GetArrayElement(input.[in],0) as in0a, GetArrayElement(input.[in],1) as in0b, GetArrayElement(input.[in],2) as in0c, GetArrayElement(input.in1,0) as in1a, GetArrayElement(input.in1,1) as in1b, GetArrayElement(input.in1,2) as in1c, GetArrayElement(input.out1,0) as out1a, GetArrayElement(input.out1,1) as out1b, GetArrayElement(input.out1,2) as out1c, GetArrayElement(input.out2,0) as out2a, GetArrayElement(input.out2,1) as out2b, GetArrayElement(input.out2,2) as out2c, GetArrayElement(input.out3,0) as out3a, GetArrayElement(input.out3,1) as out3b, GetArrayElement(input.out3,2) as out3c, flat.ArrayValue as Leak, input.leakmin, input.leakmax INTO elmontpower FROM elmoniothub as input cross apply getarrayelements(input.LeakArr) as flat;

JSON example: 1. For stream input:

  { "DT":"03.01.17 15:15:15", "in":[1,0,0], "in1":[1,1,0], "out1":[0,1,1], "out2":[1,1,0], "out3":[1,0,0], "leakmin":350, "leakmax":350, "LeakSampl":3800, "LeakArr":[150,34,56,250,12] }

2.For input without streaming data:

  { "DT":"03.01.17 15:15:15", "in":[1,0,0], "in1":[1,1,0], "out1":[0,1,1], "out2":[1,1,0], "out3":[1,0,0], "Leak":350, "leakmin":350, "leakmax":350, }

The question is how to implement a request so that when a single data for Leak arrives, it is processed and entered into the database and the data sets for the Leak are also received?

  • There is the following query in Stream Analytics, for a JSON parsm that accepts streaming array input, but ignores inputting a single metric without an array: how does this query accept parameters? show the whole picture - Anatol
  • it would be nice to have a couple of comments on the code too - Anatol
  • @Anatol JSON is sent by the device towards stream analytics and the request is parsed by JSON and the data is scattered over the table. - Vladislav E.

0