Multiple LEFT JOINS are not working as expected in Azure Stream Analytics.
I am using LEFT joins in Azure Stream Analytics and getting values for the first Two JOINS and null value for the rest of the LEFT JOIN
The below is the Json input i have used.
[
{"ID":"006XXXXX",
"ABC":
[{"E":1557302231320,"V":54.799999237060547}],
"XYZ":
[{"E":1557302191899,"V":31.0},{"E":1557302231320,"V":55}],
"PQR":
[{"E":1557302191899,"V":33},{"E":1557302231320,"V":15}],
"IJK":
[{"E":1557302191899,"V":65},{"E":1557302231320,"V":09}],
{"ID":"007XXXXX",
"ABC":
[{"E":1557302195483,"V":805.375},{"E":1557302219803,"V":0}],
"XYZ":
[{"E":1557302219803,"V":-179.0},{"E":1557302195483,"V":88}],
"PQR":
[{"E":1557302219803,"V":9.0},{"E":1557302195483,"V":98}],
"IJK":
[{"E":1557302219803,"V":1.0},{"E":1557302195483,"V":9}]
]
Below is the Query I used.
WITH
ABCINNERQUERY AS (
SELECT
event.ID as ID,
event.TYPE as TYPE,
ABCArrayElement.ArrayValue.E as TIME,
ABCArrayElement.ArrayValue.V as ABC
FROM
[YourInputAlias] as event
CROSS APPLY GetArrayElements(event.ABC) AS ABCArrayElement
),
XYZINNERQUERY AS (
SELECT
event.ID as ID,
event.TYPE as TYPE,
XYZArrayElement.ArrayValue.E as TIME,
XYZArrayElement.ArrayValue.V as XYZ
FROM
[YourInputAlias] as event
CROSS APPLY GetArrayElements(event.XYZ) AS XYZArrayElement
),
PQRINNERQUERY AS (
SELECT
event.ID as ID,
event.TYPE as TYPE,
PQRArrayElement.ArrayValue.E as TIME,
PQRArrayElement.ArrayValue.V as PQR
FROM
[YourInputAlias] as event
CROSS APPLY GetArrayElements(event.PQR) AS PQRArrayElement
),
IJKINNERQUERY AS (
SELECT
event.ID as ID,
event.TYPE as TYPE,
IJKArrayElement.ArrayValue.E as TIME,
IJKArrayElement.ArrayValue.V as IJK
FROM
[YourInputAlias] as event
CROSS APPLY GetArrayElements(event.IJK) AS IJKArrayElement
),
KEYS AS
(
SELECT
ABCINNERQUERY.ID AS ID,
ABCINNERQUERY.TIME as TIME
FROM ABCINNERQUERY
UNION
SELECT
XYZINNERQUERY.ID AS ID,
XYZINNERQUERY.TIME as TIME
FROM XYZINNERQUERY
UNION
SELECT
PQRINNERQUERY.ID AS ID,
PQRINNERQUERY.TIME as TIME
FROM PQRINNERQUERY
UNION
SELECT
IJKINNERQUERY.ID AS ID,
IJKINNERQUERY.TIME as TIME
FROM IJKINNERQUERY
)
SELECT
KEYS.ID as ID,
KEYS.TIME as TIME,
ABCINNERQUERY.ABC AS ABC,
XYZINNERQUERY.XYZ AS XYZ,
PQRINNERQUERY.PQR AS PQR,
IJKINNERQUERY.IJK AS IJK
INTO [YourOutputAlias]
FROM KEYS
LEFT JOIN ABCINNERQUERY
ON DATEDIFF(minute, KEYS, ABCINNERQUERY) BETWEEN 0 AND 10
AND KEYS.ID = ABCINNERQUERY.ID
AND KEYS.TIME = ABCINNERQUERY.TIME
LEFT JOIN XYZINNERQUERY
ON DATEDIFF(minute, KEYS, XYZINNERQUERY) BETWEEN 0 AND 10
AND KEYS.ID = XYZINNERQUERY.ID
AND KEYS.TIME = XYZINNERQUERY.TIME
LEFT JOIN PQRINNERQUERY ---From here onwards JOIN will not work. Only first two joins are working as expected.
ON DATEDIFF(minute, KEYS, PQRINNERQUERY) BETWEEN 0 AND 10
AND KEYS.ID = PQRINNERQUERY.ID
AND KEYS.TIME = PQRINNERQUERY.TIME
LEFT JOIN IJKINNERQUERY ---Once we shift this join to 1st or 2nd then it will work.
ON DATEDIFF(minute, KEYS, IJKINNERQUERY) BETWEEN 0 AND 10
AND KEYS.ID = IJKINNERQUERY.ID
AND KEYS.TIME = IJKINNERQUERY.TIME
Actual result is as below.
ID STIME ABC XYZ PQR IJK
006XXXXX 1557302231320.00 54.79999924 31 null null
006XXXXX 1557302191899.00 null 31 null null
007XXXXX 1557302195483.00 805.375 88 null null
Expected values for PQR and IJK for the corresponding time.
