Lesson 34 of 54 18 minAdvanced

JSON in SQL Server

Read, shred, build and change JSON with T-SQL: ISJSON, JSON_VALUE versus JSON_QUERY, lax and strict paths, OPENJSON with and without a schema, FOR JSON PATH, JSON_MODIFY, and JSON_OBJECT and JSON_ARRAY.

APIs, logs and web front ends speak JSON. SQL Server stores JSON as ordinary text (nvarchar) and has functions to check it, read values out of it, turn it into rows and build it from rows. This lesson uses one order as it might arrive from a web shop:

Query.sql

1DECLARE @j nvarchar(max) = N'{2  "orderRef": "WEB-1001",3  "customer": { "name": "Asha Patel", "city": "Leeds" },4  "lines": [5    { "productId": 6, "qty": 10 },6    { "productId": 8, "qty": 2 }7  ],8  "giftWrap": true,9  "note": null10}';11 12SELECT ISJSON(@j)                          AS Valid,13       JSON_VALUE(@j, '$.orderRef')        AS OrderRef,14       JSON_VALUE(@j, '$.customer.city')   AS City,15       JSON_VALUE(@j, '$.lines[1].qty')    AS SecondQty,16       JSON_VALUE(@j, '$.giftWrap')        AS GiftWrap;

Output

Valid  OrderRef  City   SecondQty  GiftWrap
-----  --------  -----  ---------  --------
    1  WEB-1001  Leeds  2          true

A path starts at $, the whole document. .name steps into a property and [n] into an array element, counting from 0. JSON_VALUE always returns nvarchar(4000) text, so true comes back as the text “true” and numbers need a CAST before you do sums with them.

JSON_VALUE or JSON_QUERY

There are two reading functions, and each returns NULL when you use the wrong one:

Function	Returns	Given an object or array	Given a scalar
`JSON_VALUE`	a scalar: text, number, true/false	NULL	the value as text
`JSON_QUERY`	an object or array, as JSON text	the JSON fragment	NULL

Query.sql

1DECLARE @j nvarchar(max) = N'{"customer":{"name":"Asha Patel","city":"Leeds"},"lines":[{"productId":6,"qty":10},{"productId":8,"qty":2}]}';2 3SELECT JSON_VALUE(@j, '$.customer')      AS ValueOfObject,4       JSON_QUERY(@j, '$.customer')      AS QueryOfObject,5       JSON_QUERY(@j, '$.customer.name') AS QueryOfScalar,6       JSON_QUERY(@j, '$.lines[0]')      AS FirstLine;

Output

ValueOfObject  QueryOfObject                         QueryOfScalar  FirstLine
-------------  ------------------------------------  -------------  ------------------------
NULL           {"name":"Asha Patel","city":"Leeds"}  NULL           {"productId":6,"qty":10}

Interactive

Reading a JSON path

{
  "orderRef": "WEB-1001",
  "customer": {
    "name": "Asha Patel",
    "city": "Leeds"
  },
  "lines": [
    {
      "productId": 6,
      "qty": 10
    },
    {
      "productId": 8,
      "qty": 2
    }
  ],
  "giftWrap": true,
  "note": null
}

JSON_VALUE(@j, '$.customer')

NULL

JSON_QUERY(@j, '$.customer')

{"name":"Asha Patel","city":"Leeds"}

An object is not a scalar, so JSON_VALUE returns NULL. JSON_QUERY returns it as JSON text, ready for OPENJSON or another JSON function.

lax and strict

By default a path is in lax mode: if the property is missing, or is the wrong kind for the function, you get NULL. That is convenient, but it hides typing mistakes. Put strict at the start of the path and the same problems raise an error:

Query.sql

1DECLARE @j nvarchar(max) = N'{"orderRef":"WEB-1001","customer":{"name":"Asha Patel"}}';2 3SELECT JSON_VALUE(@j, '$.orderref') AS LaxTypo;            -- paths are case-sensitive4SELECT JSON_VALUE(@j, 'strict $.orderref') AS StrictTypo;

Output

LaxTypo
-------
NULL

Msg 13608, Level 16, State 2, Line 4
Property cannot be found on the specified JSON path.

OPENJSON: JSON into rows

OPENJSON is a table-valued function. On its own it returns one row per property or array element, with its key, value and type (0 null, 1 string, 2 number, 3 true/false, 4 array, 5 object):

Query.sql

1DECLARE @j nvarchar(max) = N'{"orderRef":"WEB-1001","customer":{"name":"Asha Patel"},"lines":[{"productId":6,"qty":10}],"giftWrap":true,"note":null}';2 3SELECT [key], value, type4FROM OPENJSON(@j);

Output

key       value                       type
--------  --------------------------  ----
orderRef  WEB-1001                       1
customer  {"name":"Asha Patel"}          5
lines     [{"productId":6,"qty":10}]     4
giftWrap  true                           3
note      NULL                           0

Add a WITH clause and you choose the columns, their types and the path each one comes from. Point OPENJSON at the array and every element becomes a typed row, ready to join to real tables:

Query.sql

1DECLARE @j nvarchar(max) = N'{"orderRef":"WEB-1001","lines":[{"productId":6,"qty":10},{"productId":8,"qty":2}]}';2 3SELECT l.ProductId, p.Name, l.Qty, p.Price, l.Qty * p.Price AS LineValue4FROM OPENJSON(@j, '$.lines')5     WITH (ProductId int '$.productId',6           Qty       int '$.qty') AS l7JOIN dbo.Products AS p ON p.ProductId = l.ProductId;

Output

ProductId  Name               Qty  Price  LineValue
---------  -----------------  ---  -----  ---------
        6  Timber 2x4 2.4 m    10   6.80      68.00
        8  Wood screws (200)    2  11.90      23.80

This is how a stored procedure can take a whole order with all its lines as one JSON parameter and insert it with INSERT … SELECT … FROM OPENJSON(…).

JSON in a column

When JSON lives in a table, OPENJSON is used with CROSS APPLY, one document per row. A CHECK (ISJSON(col) = 1) constraint keeps bad text out:

Query.sql

1CREATE TABLE #WebEvents (2    EventId int IDENTITY PRIMARY KEY,3    Payload nvarchar(max) NOT NULL CHECK (ISJSON(Payload) = 1)4);5INSERT INTO #WebEvents (Payload) VALUES6    (N'{"type":"search","terms":["drill","cordless"]}'),7    (N'{"type":"view","productId":2}'),8    (N'{"type":"search","terms":["paint"]}');9 10SELECT e.EventId, t.value AS Term11FROM #WebEvents AS e12CROSS APPLY OPENJSON(e.Payload, '$.terms') AS t13WHERE JSON_VALUE(e.Payload, '$.type') = 'search'14ORDER BY e.EventId, t.[key];

Output

EventId  Term
-------  --------
      1  drill
      1  cordless
      3  paint

FOR JSON: rows into JSON

Add FOR JSON PATH to a query and the result becomes one JSON array, one object per row. Dots in the column aliases make nested objects, and a subquery with its own FOR JSON makes a nested array:

Query.sql

1SELECT (2    SELECT o.OrderId        AS id,3           o.OrderDate      AS [date],4           c.FirstName      AS [customer.name],5           c.City           AS [customer.city],6           (SELECT oi.ProductId AS productId, oi.Quantity AS qty7            FROM dbo.OrderItems AS oi8            WHERE oi.OrderId = o.OrderId9            ORDER BY oi.ProductId10            FOR JSON PATH)  AS lines11    FROM dbo.Orders AS o12    JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId13    WHERE o.OrderId IN (1, 4)14    ORDER BY o.OrderId15    FOR JSON PATH16) AS Json;

Output

Json
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"id":1,"date":"2024-02-03","customer":{"name":"Asha","city":"Leeds"},"lines":[{"productId":1,"qty":1},{"productId":3,"qty":2}]},{"id":4,"date":"2024-03-15","customer":{"name":"Dev","city":"Bradford"},"lines":[{"productId":6,"qty":40},{"productId":7,"qty":6}]}]

Columns that are NULL are left out of the object unless you add INCLUDE_NULL_VALUES, and WITHOUT_ARRAY_WRAPPER drops the outer [ ] when you know there is one row. Wrapping the query in SELECT (…) AS Json gives the result a normal column name.

Changing JSON: JSON_MODIFY

JSON_MODIFY(json, path, newValue) returns a copy of the document with one value changed. append adds to an array, and NULL removes a property in lax mode:

Query.sql

1DECLARE @j nvarchar(max) = N'{"orderRef":"WEB-1001","status":"new","tags":["web"]}';2 3SET @j = JSON_MODIFY(@j, '$.status', 'paid');4SET @j = JSON_MODIFY(@j, 'append $.tags', 'priority');5SET @j = JSON_MODIFY(@j, '$.paidOn', '2024-07-01');6SET @j = JSON_MODIFY(@j, '$.orderRef', NULL);7 8SELECT @j AS Changed;

Output

Changed
-----------------------------------------------------------------
{"status":"paid","tags":["web","priority"],"paidOn":"2024-07-01"}

JSON_OBJECT and JSON_ARRAY (SQL Server 2022)

For a small document from a few values, SQL Server 2022 has constructors. Unlike FOR JSON, they keep real JSON types: a bit becomes true, and a nested JSON_ARRAY stays an array:

Query.sql

1SELECT JSON_OBJECT('id': ProductId,2                   'name': Name,3                   'inStock': CAST(CASE WHEN Stock > 0 THEN 1 ELSE 0 END AS bit),4                   'sizes': JSON_ARRAY(Unit, 'trade pack')) AS Doc5FROM dbo.Products6WHERE ProductId IN (1, 9)7ORDER BY ProductId;

Output

Doc
-------------------------------------------------------------------------------
{"id":1,"name":"Claw hammer","inStock":true,"sizes":["each","trade pack"]}
{"id":9,"name":"Wall plugs (100)","inStock":false,"sizes":["box","trade pack"]}

Quick check

What does JSON_VALUE(@j, '$.customer') return when customer is an object?

Quick check

Which path reads the qty of the first line in {"lines":[{"qty":10},{"qty":2}]}?

Practice

Given {"delivery":[{"productId":4,"qty":50},{"productId":5,"qty":40},{"productId":9,"qty":25}]}, show each delivered product’s name, its current Stock, the delivered qty and the stock after the delivery.

Show a hint

OPENJSON(@d, '$.delivery') WITH (ProductId int '$.productId', Qty int '$.qty'), joined to Products.

Show the solution

Solution.sql

1DECLARE @d nvarchar(max) = N'{"delivery":[{"productId":4,"qty":50},{"productId":5,"qty":40},{"productId":9,"qty":25}]}';2 3SELECT p.Name, p.Stock, d.Qty, p.Stock + d.Qty AS NewStock4FROM OPENJSON(@d, '$.delivery')5     WITH (ProductId int '$.productId', Qty int '$.qty') AS d6JOIN dbo.Products AS p ON p.ProductId = d.ProductId7ORDER BY p.ProductId;

Output

Name              Stock  Qty  NewStock
----------------  -----  ---  --------
Cement 25 kg        300   50       350
Sand 25 kg          220   40       260
Wall plugs (100)      0   25        25

Remember

JSON is stored as nvarchar; ISJSON checks it and a CHECK constraint can enforce it.
Paths start at $, use .name and [index], count from 0 and are case-sensitive.
JSON_VALUE reads scalars (as nvarchar), JSON_QUERY reads objects and arrays; the wrong one gives NULL.
Lax paths return NULL for problems; strict paths raise errors.
OPENJSON turns JSON into rows; WITH gives typed columns; use CROSS APPLY for JSON in a column.
FOR JSON PATH builds JSON from rows; dotted aliases nest objects and subqueries nest arrays.
JSON_MODIFY changes, appends to and removes from a document.
JSON_OBJECT and JSON_ARRAY (2022) build small documents with real JSON types.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

JSON in SQL Server

JSON_VALUE or JSON_QUERY

Reading a JSON path

lax and strict

OPENJSON: JSON into rows

JSON in a column

FOR JSON: rows into JSON

Changing JSON: JSON_MODIFY

JSON_OBJECT and JSON_ARRAY (SQL Server 2022)

Remember