JSON Support in SQL Server

2020-07-02 · mssql, tsql, json

Since SQL Server 2016 you can produce and consume JSON in T-SQL without an external library. There is no dedicated JSON data type, unlike PostgreSQL's jsonb; JSON lives in nvarchar(max) columns and a set of functions parses, queries and modifies it. That design is deliberate: your existing tables and indexes keep working, and JSON columns are just text with some helper functions. It is a good fit for semi-structured attributes attached to relational rows (settings, device metadata, audit payloads) and for exchanging data with web APIs.

I was skeptical at first, since I'd rather keep things relational, but the number of API payloads and device settings I end up storing changed my mind. Here are the functions in the order I usually need them: producing JSON from a query, extracting values, shredding a document into rows, updating in place, and indexing. It all works on SQL Server 2016, 2017 and 2019 in all editions, including on Linux.

FOR JSON

SELECT o.OrderId, o.OrderDate, o.TotalAmount,
       c.Name AS [Customer.Name], c.Email AS [Customer.Email]
FROM dbo.Orders o JOIN dbo.Customers c ON o.CustomerId = c.CustomerId
WHERE o.OrderId = 1001
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

FOR JSON PATH uses dotted aliases to build nested objects, so Customer.Name becomes {"Customer": {"Name": ...}}. FOR JSON AUTO derives nesting from the join order instead. WITHOUT_ARRAY_WRAPPER returns a single object rather than a one-element array; leave it out for result sets. Add INCLUDE_NULL_VALUES if consumers expect nulls rather than missing keys. Nested arrays come from a correlated subquery with its own FOR JSON PATH.

Validating and extracting values

Assume a table with a JSON column:

CREATE TABLE dbo.Devices (
    DeviceId INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Attributes NVARCHAR(MAX) NULL CONSTRAINT CK_Devices_Attributes CHECK (ISJSON(Attributes) = 1)
);
INSERT dbo.Devices (Name, Attributes)
VALUES ('sensor-01', N'{"firmware":"2.4.1","location":{"site":"WH1","rack":12},"tags":["temp","outdoor"]}');

The CHECK (ISJSON(...) = 1) constraint is the one piece of enforcement you get. I add it to every JSON column, because the first bad payload will happen sooner than you think.

SELECT Name,
       JSON_VALUE(Attributes, '$.firmware') AS firmware,
       JSON_VALUE(Attributes, '$.location.site') AS site,
       JSON_VALUE(Attributes, '$.tags[0]') AS first_tag,
       JSON_QUERY(Attributes, '$.location') AS location_json
FROM dbo.Devices;

JSON_VALUE returns a scalar as nvarchar(4000); JSON_QUERY returns an object or array as text. Using JSON_VALUE on an object returns NULL in lax mode (the default) or an error in strict mode ('strict $.location').

OPENJSON: documents into rows

DECLARE @json NVARCHAR(MAX) = N'[
  {"sku":"A100","qty":2,"price":19.90},
  {"sku":"B200","qty":1,"price":249.00}
]';

SELECT * FROM OPENJSON(@json)
WITH (
    sku   VARCHAR(20)   '$.sku',
    qty   INT           '$.qty',
    price DECIMAL(10,2) '$.price'
);

The WITH clause gives you a typed table you can INSERT ... SELECT from, which is how I load API payloads. Without WITH, OPENJSON returns key, value and type columns, useful for exploring an unknown document. To expand a nested array per row, use CROSS APPLY:

SELECT d.Name, t.value AS tag
FROM dbo.Devices d
CROSS APPLY OPENJSON(d.Attributes, '$.tags') t;

OPENJSON requires database compatibility level 130 or higher; on a database restored from an older version you get "Msg 208: Invalid object name 'OPENJSON'" until you raise it.

Changing JSON in place

UPDATE dbo.Devices
SET Attributes = JSON_MODIFY(
                    JSON_MODIFY(Attributes, '$.firmware', '2.5.0'),
                    'append $.tags', 'calibrated')
WHERE Name = 'sensor-01';

JSON_MODIFY returns the whole document with one change, so nested calls handle multiple edits. Set a value to NULL to delete the key; use 'append $.array' to push to an array. Numbers passed as strings become JSON strings; cast explicitly (CAST(12 AS INT)) to store a number.

Indexing JSON properties

Because JSON is text, WHERE JSON_VALUE(Attributes, '$.location.site') = 'WH1' scans the table. Add a computed column and index it; the optimiser matches the expression:

ALTER TABLE dbo.Devices ADD Site AS JSON_VALUE(Attributes, '$.location.site');
CREATE INDEX IX_Devices_Site ON dbo.Devices (Site);

The computed column does not need PERSISTED for the index. For very large documents, a full-text index on the column plus CONTAINS can help with LIKE-style searches, but I always try the computed column first.

Did the index get used?

SELECT Name, Attributes FROM dbo.Devices WHERE Site = 'WH1';
SET STATISTICS IO ON;
SELECT Name FROM dbo.Devices WHERE JSON_VALUE(Attributes, '$.location.site') = 'WH1';

The execution plan for the second query should show an index seek on IX_Devices_Site even though the query text uses the function directly. Also try inserting invalid JSON and confirm the check constraint rejects it with "The INSERT statement conflicted with the CHECK constraint".

Notes

"Msg 13609: JSON text is not properly formatted. Unexpected character '...' is found at position N." Usually single quotes inside the JSON or a trailing comma. Validate with SELECT ISJSON(@json) and remember the string must be nvarchar (prefix N) to avoid mangled Unicode.

JSON_VALUE truncates long values. It returns at most 4000 characters; use JSON_QUERY for longer strings, or OPENJSON ... WITH (col NVARCHAR(MAX) '$.path').

Property names are case-sensitive. $.Firmware and $.firmware are different paths regardless of the database collation.

Performance with huge documents. Every function call parses the document from the start. If you extract ten properties from a 1 MB document, use one OPENJSON ... WITH call with ten columns rather than ten JSON_VALUE calls.

It won't replace a document database, and I don't want it to. For flexible attributes next to relational data, it's plenty.