Housing intake stores applicant preferences as JSON and must read a single nested preference key for routing. Which function should extract that scalar value?
Select an answer to reveal the explanation.
Short Explanation
JSON_VALUE is the precise tweezers—grab that one preference key and route the file. You do not need to dismantle the whole document into tables just to read a single path.
Full Explanation
Housing intake that stores applicant preferences as JSON and must read one nested preference key for routing needs a scalar path extract. JSON_VALUE returns that nested value as a string (cast as needed) without rebuilding the document, which fits a routing decision such as “language” or “mobility_need” inside a procedure or computed expression.
OPENJSON is powerful for shredding objects and arrays into rows, but forcing a full schema rebuild for every single-key lookup is heavier than necessary when only one scalar path is required. JSON_ARRAYAGG aggregates values into a JSON array; it is a construction/aggregation function, not a precision extractor for one nested key. REGEXP_SPLIT_TO_TABLE tokenizes text into rows and is the wrong tool for structured JSON path access—fragile and not schema-aware.
Exam caveat: JSON_VALUE returns NULL for missing paths or non-scalars; use JSON_QUERY when you need an object/array fragment, and OPENJSON when you need relational rows. Operational check: load an applicant JSON with a known nested preference, call JSON_VALUE with the exact path used in routing, and assert the returned scalar matches; then remove the key and confirm NULL-handling in the router.