Stream Analytics - query nested arrays returns 0 results

Question

I have an array of messages; within each message is another array of start/stop values. I can get the array of messages fine, but getting the next level of nested start/stop values returns 0 records while executing a stream analytics query in Azure.

Here is the Stream Analytics query.

WITH 
main AS
(
    SELECT          message.ArrayValue.header.messageId,
                    message.ArrayValue.startStopBlock as SSBlock                    

    FROM            IoTHub i
    CROSS APPLY     GetArrayElements(i.[STARTSTOPTIME:topic].message) AS 
message
)

SELECT              m.messageId,               
                    SSEntry.ArrayValue.start,
                    SSEntry.ArrayValue.stop

FROM            main m
CROSS APPLY     GetArrayElements(m.SSBLOCK.ArrayValue.startStop) AS SSEntry

Here is the json

{
	"@xsi:schemaLocation" : "",
	"nextBuffer" : {
		"url" : "",
		"moreData" : "false"
	},
	"message" : [{
			"header" : {
				"messageId" : "951262328",
			},
			"totalStartStops" : "2",
			"startStopBlock" : {
				"startStop" : [{
						"start" : "2017-05-16 14:11:01",
						"stop" : "2017-05-16 14:14:16",
						"operatorId" : "0"
					}, {
						"start" : "2017-05-16 14:38:45",
						"stop" : "2017-05-16 14:44:19",
						"operatorId" : "0"
					}
				]
			}
		}, {
			"header" : {
				"messageId" : "951266462",
			},
			"totalStartStops" : "2",
			"startStopBlock" : {
				"startStop" : [{
						"start" : "2017-05-16 14:08:09",
						"stop" : "2017-05-16 14:08:20",
						"operatorId" : "-1"
					}, {
						"start" : "2017-05-16 14:54:38",
						"stop" : "2017-05-16 15:01:17",
						"operatorId" : "-1"
					}
				]
			}
		}
	]
}

Answer 1

Per my understanding, you could try to retrieve the nested arrays in your first query by changing message.ArrayValue.startStopBlock as SSBlock to message.ArrayValue.startStopBlock.startStop as SSBlock. Here is my test, you could refer to it:

Query

WITH main AS
(
    SELECT  
      message.ArrayValue.header.messageId,
      message.ArrayValue.startStopBlock.startStop as SSBlock                    
    FROM IoTHub i
    CROSS APPLY GetArrayElements(i.message) AS message
)

SELECT
  m.messageId,               
  SSEntry.ArrayValue.start,
  SSEntry.ArrayValue.stop
FROM main m
CROSS APPLY GetArrayElements(m.SSBLOCK) AS SSEntry

Result