Working with JSON in SQL Server: OPENJSON, JSON_VALUE and FOR JSON

Modern applications send and receive JSON everywhere, and SQL Server has built-in functions to read, write and query it, available since SQL Server 2016 and in Azure SQL. Used well, they let you store flexible data and exchange it with APIs without extra code. Used badly, they create slow, hard-to-query tables. Here is the practical toolkit.

Working with JSON in SQL Server

Storing JSON

Traditionally JSON is stored in an NVARCHAR column, with a check constraint to keep it valid:

CREATE TABLE dbo.EmployeeSettings (
    EmployeeId INT PRIMARY KEY,
    Settings   NVARCHAR(MAX) NOT NULL
        CONSTRAINT CK_EmployeeSettings_IsJson CHECK (ISJSON(Settings) = 1)
);

INSERT INTO dbo.EmployeeSettings VALUES
(1, N'{"theme":"dark","language":"en","notifications":{"email":true,"sms":false},"roles":["payroll","hr"]}');

Azure SQL Database and newer SQL Server versions also offer a native json data type that stores JSON more efficiently. Check what your version supports; the functions below work with both.

Read a single value: JSON_VALUE

SELECT
    EmployeeId,
    JSON_VALUE(Settings, '$.theme')                AS Theme,
    JSON_VALUE(Settings, '$.notifications.email')  AS EmailOn
FROM dbo.EmployeeSettings;

JSON_VALUE returns a scalar as text (up to 4,000 characters). For objects or arrays it returns NULL in the default lax mode; use JSON_QUERY for those.

Read an object or array: JSON_QUERY

SELECT JSON_QUERY(Settings, '$.roles') AS Roles   -- returns ["payroll","hr"]
FROM dbo.EmployeeSettings;

Turn JSON into rows: OPENJSON

OPENJSON is the workhorse: it turns a JSON array into a table you can join and filter. With a WITH clause you define typed columns:

DECLARE @json NVARCHAR(MAX) = N'[
  {"code":"E100","name":"Asha","dept":10},
  {"code":"E101","name":"Ravi","dept":20}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    EmployeeCode VARCHAR(20)   '$.code',
    FullName     NVARCHAR(100) '$.name',
    DepartmentId INT           '$.dept'
);

This is a clean way to pass a whole list from an application to a stored procedure in one parameter and insert it with a single INSERT ... SELECT. OPENJSON requires database compatibility level 130 or higher.

To expand an array stored inside a column, combine it with CROSS APPLY:

SELECT s.EmployeeId, r.value AS Role
FROM dbo.EmployeeSettings s
CROSS APPLY OPENJSON(s.Settings, '$.roles') r;

Change JSON: JSON_MODIFY

UPDATE dbo.EmployeeSettings
SET Settings = JSON_MODIFY(Settings, '$.theme', 'light')
WHERE EmployeeId = 1;

-- Append to an array
UPDATE dbo.EmployeeSettings
SET Settings = JSON_MODIFY(Settings, 'append $.roles', 'admin')
WHERE EmployeeId = 1;

Return results as JSON: FOR JSON

SELECT EmployeeId AS id, FullName AS name, DepartmentId AS [department.id]
FROM dbo.Employee
WHERE IsActive = 1
FOR JSON PATH, ROOT('employees');

FOR JSON PATH uses dotted column aliases to build nested objects. FOR JSON AUTO builds the shape from the tables in the query.

Index a JSON property you filter on

Filtering on JSON_VALUE in a WHERE clause scans every row and parses JSON each time. If you filter on a property often, expose it as a computed column and index it:

ALTER TABLE dbo.EmployeeSettings
ADD Language AS JSON_VALUE(Settings, '$.language');

CREATE INDEX IX_EmployeeSettings_Language
ON dbo.EmployeeSettings (Language);

Queries using WHERE JSON_VALUE(Settings, '$.language') = 'en' can then use the index.

When not to use JSON

  • Data you filter, join or aggregate on regularly belongs in proper columns with proper types.
  • Data that needs constraints, such as foreign keys, belongs in tables.
  • JSON is a good fit for optional settings, audit payloads, API request copies and attributes that vary by record.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more