Query JSON on Azure SQL Database with the native type
Two generations of JSON support exist here and far more has been written about 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.
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.
Scope
- Where a document column belongs in a table design is
design-azure-sql-schema. - General T-SQL dialect is
t-sql-correctness.
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 CREATE JSON INDEX page lists Azure SQL Database in its applies-to
banner, and its json data type page states that the type and JSON indexes are generally
available on Azure SQL Database. The index builds and serves queries on the local Azure SQL
Database container.
For one equality predicate on a nested property, nvarchar(max) and json without a JSON index
cost about the same, because both read every page of the base table. With a JSON index the same
query takes a small fraction of the reads and the CPU.
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 an array out of nvarchar(max) documents costs several times less CPU with WITH
than the default output plus a JSON_VALUE per field. The plans differ too, and reading them is
read-execution-plan. Performance is the smaller half:
- A bad value fails loudly instead of arriving as text.
WITH (qty int '$.qty')over"qty": "abc"raisesMsg 245; the default output hands backabcasnvarchar(max). - A nested object or array needs
AS JSON, which requiresnvarchar(max); anything else isMsg 13618. Without it the column isNULLin lax mode rather than the nested document. - Paths and
keyare case-sensitive, matched BIN2 and collation-unaware, so on a case-insensitive databaseWHERE [key] = N'orderid'matches nothing againstorderId. - A missing property is
NULLin lax mode andMsg 13608understrict, 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, 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': it
fails at 100 and 120 and works at 130, while the json type and CREATE JSON INDEX work 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, which can return a stale value 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. go-sqlcmd 1.x, the 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, andRETURNINGis closed to it. - Do not call
nvarchar(max)plus functions deprecated. It is not, and on scalar reads it is the cheaper plan: reading four properties from every row costs several times less CPU withJSON_VALUEthan withOPENJSON ... WITH. - Do not reach for
OPENJSONto read one scalar property, and do not lower-case a path or akeycomparison, which quietly matches nothing. - Do not assume a
jsoncolumn 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 missingWITHorAS JSON, and a lower-cased path are all invisible in the result set.