Transit ops must build a nested JSON array of stop names per route for a mobile API. Which combination best produces that civic route payload?
Select an answer to reveal the explanation.
Short Explanation
Each route is a necklace and every stop is a bead—CTE lines up the beads, then JSON_ARRAYAGG strings them into one JSON array per route.
Full Explanation
Staging stops in a CTE and then using JSON_ARRAYAGG to aggregate stop names per route is the correct combination for transit ops building a nested JSON array of stop names per route for a mobile API. The CTE organizes the civic stop rows; JSON_ARRAYAGG collapses stop names into a JSON array grouped by route, producing the nested payload shape mobile clients expect.
Applying only a CHECK constraint on route_id fails by concept because CHECK validates allowed values; it does not construct JSON arrays of stops. Using MATCH without JSON aggregation fails because MATCH traverses SQL graph patterns and does not, by itself, emit nested stop-list JSON per route. Relying solely on REGEXP_INSTR fails because it finds pattern positions in strings; it neither groups stops by route nor builds JSON arrays for the API.
Exam caveat: DP-800 JSON constructors/aggregates (such as JSON_ARRAYAGG) are the tools for nested API payloads; constraints, graph MATCH, and regex position functions solve different problems. When the stem asks for nested JSON arrays per group, look for aggregation into JSON—not validation or graph traversal alone.
Operational check: For a sample route, compare the JSON array length and order to the ordered stop list in the scheduling system; validate the API consumer can parse the array; and add regression tests so schedule changes still produce complete municipal stop payloads.