t-sql-json-and-openjson
Installation
SKILL.md
Query JSON on Azure SQL Database with the native type
Two generations of JSON support exist here and a model has read far more of the first: a document
in nvarchar(max) read with functions. The second is a real json type with its own storage,
validation and index. Both work, and the differences are not only speed. Measured 2026-08-28
against a live engine reporting EngineEdition 5 and Edition SQL Azure; the Learn pages for
OPENJSON, JSON_VALUE and CREATE JSON INDEX were re-read 2026-09-03.
Open references/json-type-errors-and-timings.md when
an error number you hit is not listed here, or before you change one that is.
What the json type actually changes
nvarchar(max) |
json |
|
|---|---|---|
| Invalid JSON on insert | accepted silently | rejected, Msg 13609 |
CREATE JSON INDEX |
no, Msg 13680 |
yes, one per column |
JSON_VALUE(..., path RETURNING type) |
no, json input only | yes |
=, ORDER BY, GROUP BY, DISTINCT, LIKE |
yes | no: Msg 402, 13636, 421, 8116 |
| Text preserved byte for byte | yes | no, reformatted and duplicate keys dropped |