SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillst-sql-json-and-openjson

t-sql-json-and-openjson

Data & analytics

View repository

Updated 10-16 00:31

About this skill

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

Validation. nvarchar(max) takes {not json at all without a word, and the damage surfaces months later as a JSON_VALUE returning NULL in a report.

CREATE TABLE dbo.doc_nv   (id int IDENTITY PRIMARY KEY CLUSTERED, payload nvarchar(max) NULL);
CREATE TABLE dbo.doc_json (id int IDENTITY PRIMARY KEY CLUSTERED, payload json NULL);
CREATE TABLE dbo.doc_chk  (id int IDENTITY PRIMARY KEY CLUSTERED, payload nvarchar(max) NULL
    CONSTRAINT ck_doc_chk_json CHECK (ISJSON(payload) = 1));
INSERT INTO dbo.doc_nv   (payload) VALUES (N'{not json at all');   -- no message of any kind
INSERT INTO dbo.doc_json (payload) VALUES (N'{not json at all');   -- Msg 13609
INSERT INTO dbo.doc_chk  (payload) VALUES (N'{not json at all');   -- Msg 547

The constraint buys the refusal back for a column that must stay nvarchar(max), and added later it fails if a bad row is already there, which is the audit. ISJSON still returns 1 over a json column; to demand a property rather than mere well-formedness Learn uses CHECK (JSON_PATH_EXISTS(payload, '$.basket') = 1).

Indexing. The difference that cannot be worked around:

CREATE TABLE dbo.orders_json (id int IDENTITY PRIMARY KEY CLUSTERED, payload json NOT NULL);
CREATE JSON INDEX jix_orders ON dbo.orders_json (payload)
    FOR ('$.customer', '$.lines') WITH (OPTIMIZE_FOR_ARRAY_SEARCH = ON, FILLFACTOR = 80);

The same statement against dbo.doc_nv returns Msg 13680. Learn adds the fence: the table needs a clustering key, one index per json column and 249 per table, the default path set is $, listed paths may not overlap, builds are offline only. It serves JSON_PATH_EXISTS, JSON_CONTAINS and JSON_VALUE compared with =, RETURNING cast included, but not LIKE or IS [NOT] NULL. Learn's page omits Azure SQL Database from its applies-to banner and calls the feature preview, yet it built and served queries on the engine measured here.

One equality predicate on a nested property over 20,000 documents: 958 logical reads and 29 ms of CPU on nvarchar(max), 915 and 23 ms on json without a JSON index, 142 and 5 ms with one.

The alternative on either storage is the promoted column, a PERSISTED computed column over JSON_VALUE with an ordinary index, which design-azure-sql-schema owns. Converting is close to one way: ALTER COLUMN payload json fails with Msg 13609 if any row is invalid, and the reverse is refused in place with Msg 257.

OPENJSON, and why the WITH clause is not a style choice

Without WITH, OPENJSON returns key in a BIN2 collation, value as nvarchar(max), and type numbered 0 null, 1 string, 2 number, 3 true/false, 4 array, 5 object, first level only. With WITH it returns the columns asked for, typed, and those three are then unavailable.

SELECT o.id, l.sku, l.qty, l.price, l.tags
FROM dbo.orders AS o
CROSS APPLY OPENJSON(o.payload, '$.lines')
    WITH (sku nvarchar(10), qty int, price decimal(10,2),
          tags nvarchar(max) '$.tags' AS JSON) AS l;

A path is optional where the column name equals the property name, as sku and qty show. Shredding 40,000 array elements out of 20,000 nvarchar(max) documents cost 94 to 130 ms of CPU with WITH, against 375 to 395 ms for the default output plus a JSON_VALUE per field, across three runs. The plans differ too, and reading them is read-execution-plan. Performance is the smaller half:

  1. A bad value fails loudly instead of arriving as text. WITH (qty int '$.qty') over "qty": "abc" raises Msg 245; the default output hands back abc as nvarchar(max).
  2. A nested object or array needs AS JSON, which requires nvarchar(max); anything else is Msg 13618. Without it the column is NULL in lax mode rather than the nested document.
  3. Paths and key are case-sensitive, matched BIN2 and collation-unaware, so on a case-insensitive database WHERE [key] = N'orderid' matched nothing against orderId.
  4. A missing property is NULL in lax mode and Msg 13608 under strict, and lax is the default when no mode is written. Choose deliberately.

The 4000 character cliff, and the two ways past it

JSON_VALUE without RETURNING is typed nvarchar(4000), and Learn is explicit that above 4000 characters it returns NULL in lax mode and an error in strict mode; measured, strict raised Msg 13625, "would be truncated". That is data loss dressed as a missing key, and both escapes fit in one statement:

DECLARE @j json = JSON_OBJECT('big': REPLICATE(CAST(N'x' AS nvarchar(max)), 5000));
SELECT JSON_VALUE(@j, '$.big')                              AS lax_returns_null,
       LEN(JSON_VALUE(@j, '$.big' RETURNING nvarchar(max))) AS returning_len,
       (SELECT LEN(v) FROM OPENJSON(@j) WITH (v nvarchar(max) '$.big')) AS openjson_len;

RETURNING is documented only for a json input, one more reason the storage choice is not cosmetic; OPENJSON ... WITH works on both. It also takes the numeric, date and time types, so JSON_VALUE(payload, '$.customer.id' RETURNING int) = 16167 is both typed and indexable.

Compatibility level 130 is real, and almost never your problem

OPENJSON is hidden below level 130 and returns Msg 208, Invalid object name 'OPENJSON': measured failing at 100 and 120, working at 130, while the json type and CREATE JSON INDEX worked at 120. The requirement is real and nearly never the fault, because a database created on Azure SQL Database today starts far above 130.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'CompatibilityLevel') AS level;

Read it that way rather than from sys.databases, observed stale in the same session as an ALTER DATABASE. Treat raising the level as a separate migration change that requires workload testing. If it cannot move, Learn documents a second door: ALLOW_BUILTIN_TVF_IN_ALL_COMPAT_LEVELS, a database-scoped configuration that exempts OPENJSON and the other built-in table-valued functions.

Check it worked

Five traps from this page in one row:

DECLARE @j json = N'{  "orderId" : 7,   "customer" : {"id":"C1"}, "dup": 1, "dup": 2 }';
SELECT CAST(@j AS nvarchar(max))            AS normalized,
       JSON_VALUE(@j, '$.orderid')          AS wrong_case,
       JSON_VALUE(@j, '$.orderId')          AS right_case,
       (SELECT c FROM OPENJSON(@j) WITH (c nvarchar(max) '$.customer'))         AS without_as_json,
       (SELECT c FROM OPENJSON(@j) WITH (c nvarchar(max) '$.customer' AS JSON)) AS with_as_json;

normalized       {"orderId":7,"customer":{"id":"C1"},"dup":1}
wrong_case       NULL
right_case       7
without_as_json  NULL
with_as_json     {"id":"C1"}

Whitespace and a second dup in normalized mean the column is nvarchar(max), not json; a NULL in right_case means the property is spelled some other way. In a pipeline pass both error flags, because -b alone sets a non-zero exit only above severity 10 and without -m-1 a severity 10 message prints with no Msg number to grep for:

sqlcmd -S <server>,1433 -d <database> -b -m-1 -i check-json.sql

-m-1 is an ODBC sqlcmd instruction, meaning the 18.x build from mssql-tools18 or the Microsoft command line utilities. Measured 2026-09-05, go-sqlcmd 1.10.0, the 1.x build brew install sqlcmd and winget install sqlcmd install, prints no Msg header on a severity 10 message at any -m value, so on that build there is no number to grep for whatever -m says. The numbers in this skill that matter most, 13609, 13618, 13608 and 13625, are all severity 16 and print on either build; it is the severity 10 case this flag is for.

Do not

  • Do not default to nvarchar(max) because it is familiar: it accepts invalid JSON, cannot carry a JSON index, and RETURNING is closed to it.
  • Do not call nvarchar(max) plus functions deprecated. It is not, and on scalar reads it is the cheaper plan: four properties from each of 20,000 rows cost 81 ms of CPU with JSON_VALUE against 735 ms for OPENJSON ... WITH.
  • Do not reach for OPENJSON to read one scalar property, and do not lower-case a path or a key comparison, which quietly matches nothing.
  • Do not assume a json column hands back the text that went in. It is reparsed, whitespace goes and a duplicate key is dropped, so if a signature covers the exact bytes keep the raw text too.
  • Do not review only the query. An unvalidated nvarchar(max), a missing WITH or AS JSON, and a lower-cased path are all invisible in the result set.