Permit applications store inspection checklists as JSON arrays that must become relational checklist rows. Which T-SQL function should shred those arrays?
Select an answer to reveal the explanation.
Short Explanation
OPENJSON is the unpacker: it takes the JSON checklist crate and lays each item out as a normal row. JSON_VALUE is for plucking one scalar field, not exploding an array into a table.
Full Explanation
Permit applications that store inspection checklists as JSON arrays must become relational checklist rows for querying, joining to findings, and enforcing completion. OPENJSON shreds the array into a rowset (with optional WITH schema) so each checklist item becomes a row the permits database can insert into a child table or join in place. That is the standard T-SQL bridge from JSON payload to relational inspection work.
JSON_VALUE extracts a scalar from a path; pointed at an array it may return NULL or a stringified fragment—not a set of checklist rows. VECTOR_DISTANCE compares embeddings for similarity search; it does not shred JSON arrays into relational rows. EDIT_DISTANCE measures string similarity; it does not produce checklist rowsets from permit JSON.
Exam caveat: use OPENJSON with an explicit schema when item fields are stable; validate JSON with ISJSON before shredding untrusted submissions. Operational check: pass a sample permit payload with three checklist items through OPENJSON, confirm three rows with expected keys, and insert them into the checklist table inside a transaction that you can roll back in lab. After shredding, enforce relational constraints on the checklist child table (required item codes, unique item per permit) so JSON flexibility does not bypass inspection completeness rules. Reject or quarantine payloads that fail ISJSON or that produce unexpected OPENJSON keys before they enter the permits database.