County open data must publish each restaurant inspection with its violation codes collected into one JSON array. Which function should aggregate those codes?
Select an answer to reveal the explanation.
Short Explanation
JSON_ARRAYAGG is the basket: drop every violation code for that inspection into one JSON array for the open-data feed. JSON_VALUE only pulls one scalar, so it cannot build the list.
Full Explanation
County open data that must publish each restaurant inspection with its violation codes collected into one JSON array needs aggregation into JSON, not scalar extraction. JSON_ARRAYAGG (or the platform’s equivalent array aggregation) groups violation code rows per inspection and emits a single JSON array column suitable for an open-data feed or API payload.
JSON_VALUE reads one scalar path; it cannot invent an array from multiple violation rows. CHECK constraints validate inserts; they do not concatenate violation codes into JSON arrays as an aggregation feature. VECTOR_NORMALIZE operates on vector embeddings for ML similarity workflows, not on packing municipal violation codes into JSON arrays for open data.
Exam caveat: pair array aggregation with GROUP BY inspection_id and decide NULL/empty-array behavior for clean inspections. Operational check: for an inspection with three violation rows, aggregate and confirm a three-element JSON array; for a clean inspection, confirm the documented empty or NULL behavior before publishing the feed. Order elements inside the array only if the open-data contract requires a stable sequence—otherwise document that array order is undefined. Export the aggregated column alongside inspection identity and date so consumers can join violations without round-tripping to restricted base tables.