# T SQL JSON And Openjson

> Use when storing, querying or reviewing JSON in Azure SQL Database: json type or nvarchar(max), OPENJSON, JSON_VALUE returning NULL. Has verified behavior common answers miss, e.g. nvarchar(max) silently stores invalid JSON.

- Skill: `microsoft/t-sql-json-and-openjson` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx get-skill add microsoft/t-sql-json-and-openjson`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/t-sql-json-and-openjson/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/t-sql-json-and-openjson

---


# 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](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.

```sql
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:

```sql
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.

```sql
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:

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'` matches 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, `Msg 13625`, "would be
truncated". That is data loss dressed as a missing key, and both escapes fit in one statement:

```sql
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.

```sql
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:

```sql
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;
```

```text
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:

```bash
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, 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: reading four properties from every row costs **several times less CPU** with
  `JSON_VALUE` than with `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.

