Develop data processing →mediumMultiple Choice

DP-203 Develop data processing Practice Question

You are building an Azure Stream Analytics job that reads JSON events from an Azure Event Hub. Each event contains a nested array property named 'readings' with multiple sensor values. You need to output one row per sensor reading to an Azure Synapse Analytics dedicated SQL pool. The query must flatten the array. Which query syntax should you use?

⚠ Common exam trap

The trap here is assuming that ANSI SQL set-returning functions such as UNNEST or Spark-style EXPLODE work in Azure Stream Analytics, when only GetArrayElements with CROSS APPLY is supported.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

SELECT deviceId, reading.value FROM input CROSS APPLY GetArrayElements(input.readings) AS reading

Azure Stream Analytics flattens nested arrays using the built-in GetArrayElements function combined with CROSS APPLY. GetArrayElements returns a table with a 'value' column for each element, and CROSS APPLY expands those elements into separate rows. Referencing reading.value projects the scalar sensor value, producing one output row per reading as required.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
SELECT deviceId, readings.value FROM input UNNEST(input.readings) AS readings
Why it's wrong here
UNNEST is not a supported operator in Azure Stream Analytics query language. Although UNNEST is valid in other SQL engines, the ASA engine will reject this syntax at compile time, so the job would fail to start. The scenario requires a working ASA query, and UNNEST cannot flatten the nested array here.
✗
SELECT deviceId, reading FROM input CROSS APPLY GetArrayElements(input.readings) AS reading
Why it's wrong here
This syntax references the alias directly instead of its value property. In Azure Stream Analytics, GetArrayElements returns a column named 'value' within the alias, so selecting 'reading' alone does not project the sensor value. The query would compile but return an object reference rather than the scalar reading, failing the requirement to output one row per sensor reading.
✗
SELECT deviceId, EXPLODE(input.readings) AS value FROM input
Why it's wrong here
EXPLODE is a Hive/Spark SQL function, not part of the Azure Stream Analytics query language. Using it would cause a compilation error in the ASA job, preventing any output. The scenario is specifically an ASA job reading from Event Hubs, so Spark-style functions are the wrong tool and will not flatten the array.
✓
SELECT deviceId, reading.value FROM input CROSS APPLY GetArrayElements(input.readings) AS reading
Why this is correct
This is correct because Azure Stream Analytics supports GetArrayElements as a streaming function that returns a table of array elements, and CROSS APPLY is the documented way to flatten a nested array into multiple rows. The alias reading exposes the element via reading.value, producing one output row per sensor reading, which matches the required one-row-per-reading output to the dedicated SQL pool.

Go deeper

DP-203 Develop data processing Practice Question

Why each option matters

Option-by-option breakdown

Related to this question