CODEWITHSUNDEEP
jsonazure-sql-databaseazure-stream-analytics
Swapnil Kushwaha
Swapnil Kushwaha

Reputation: 1

How to query JSON to deplane JSON containg more than one array values in Stream analytics

I want to query below JSON input data received from IotHub in stream analytics

{
            "DeviceId": "12355",
            "Message": {
                        "TimeStamp": [{"Time": "1"}, {"Time": "2"}],
                        "Streak": "4",
                        "Checkin": [{"Time": "3"}, {"Time": "4"}],
                        "Status": ""
            }
}

I have used below query which is giving me four outputs after applying cross I just want two outputs

I want my output to be in below format. 

DeviceID    Streak  TimeStamp   Checkin  Status
  "12355"   "4"     "1"         "3"      ""
  "12355"   "4"     "2"         "4"      ""

This is the query I wrote using Cross Apply

SELECT
 [Stat].[DeviceId] AS [DeviceId],

 [Stat].[Message].[Streak] AS [Streak],
 [Stat].[Message].[Status] AS [Status],
 [Stat].[Message].[Error] AS [Error],
 [Stat].[UseTime].[Time] AS [Checkin],
 [Stat].[Open].[Time] AS [OpenTime]
 INTO
 Messages
FROM
(
 SELECT
 [EventAlias].*,
 [Checkin].[ArrayValue] AS [UseTime],
[OutputTime].[ArrayValue] AS [Open]
 FROM [SmartCapData] AS [EventAlias]
 CROSS APPLY GetArrayElements(EventAlias.Message.Checkin) AS [Checkin]
 CROSS APPLY GetArrayElements(EventAlias.Message.Timestamp) AS [OutputTime]
) AS Stat

In the above it is applying cross over cross I want to use it in parallel, Thanks in advance :)

Upvotes: 0

Views: 60

Answers (1)

Konstantin Zoryn
Konstantin Zoryn

Reputation: 621

This query should do the trick:

SELECT
    inputevents.DeviceId,
    inputevents.Message.Streak, 
    inputevents.Message.Status, 
    a.ArrayValue.Time AS Timestamp, 
    GetRecordPropertyValue(GetArrayElement(inputevents.Message.Checkin, a.ArrayIndex), 'Time') AS Checkin
FROM inputevents
CROSS APPLY GetArrayElements(inputevents.Message.TimeStamp) a

Alternative way to do complex JSON transformations is to use custom JavaScript function.

Upvotes: 0

Related Questions