Access Nested JSON Array using stream analytics

Question

I am fairly new to stream analytics and I have been trying to parse a nested JSON Array . I need to access the data under collections. The query I am using is as below. I also tried using getarrayelement before collections but that did not work either. Any help would be highly appreciated.

SELECT
i.headers.eesEventID AS [EventId]
,(GetArrayElement(i.atomicOperations,0)).objectType AS [ObjectType]
,(GetArrayElement(i.atomicOperations,0)).objectValue.id AS [ColorID]
,(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.yellow.collections.minimumUnits AS [CouponMinimumQuantityTest]
, COALESCE(
(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.yellow.collections.minimumUnits
,(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.red.collections.minimumUnits
,(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.white.collections.minimumUnits
,(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.blue.collections.minimumUnits
) AS [CouponMinimumQuantity]
, COALESCE(
(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.yellow.collections.minSP
,(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.red.collections.minSP
,(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.white.collections.minSP
,(GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.blue.collections.minSP
) AS [CouponMinimumProductSpend]

INTO
[eventhub-stream-output]
FROM
[eventhub-stream-input] i
PARTITION BY [ColorID]
WHERE cast((GetArrayElement(i.atomicOperations,0)).objectType AS nvarchar(MAX)) = 'ColorEntity'

The JSON I receive is as below

{
"headers": {
"eesEventId": "1568953"
},
"operations": [
{
"objectType": "ColorEntity",
"operationType": "CREATE",
"objectValue": {
"id": "780051",
"offer": {
"boltOn": null,
"colors": {
"basket": null,
"product": {
"yellow": {
"collections": [
{
"minSP": 1,
"minimumUnits": 1
}
],
"logicalOperator": "OR"
},
"red": {
"collections": [
{
"minSP": 1,
"minimumUnits": 1
}
],
"logicalOperator": "OR"
},
"white": {
"collections": [
{
"minSP": 1,
"minimumUnits": 1
}
],
"logicalOperator": "OR"
},
"blue": {
"collections": [
{
"minSP": 1,
"minimumUnits": 1
}
],
"logicalOperator": "OR"
}
}
}
}
}
}
]
}

Answer 1

I was able to do it using nested getarrayelement like below

(GetArrayElement((GetArrayElement(i.atomicOperations,0)).objectValue.offer.colors.product.yellow.collections,0)).minimumUnits AS [CouponMinimumQuantityTest]