JSON in SQL Server
SQL Server can store, query and produce JSON. This lesson shows how to return query results as JSON, read values with JSON_VALUE, turn a JSON array into rows with OPENJSON, validate with ISJSON, and index a JSON property for fast searches.
- FOR JSON PATHRows to JSON text
- JSON_VALUERead one value, such as $.color
- JSON_QUERYRead an object or array
- OPENJSONJSON array to rows
- JSON_MODIFYChange one property
- ISJSONCheck that text is valid JSON
When JSON in the database makes sense
JSON suits data whose shape varies per row, such as product attributes or settings, and data you receive from or send to an API as a whole. Data you filter, join or update often still belongs in normal columns.
SQL Server stores JSON in nvarchar(max) columns (SQL Server 2025 and Azure SQL also offer a native json type). Add a CHECK (ISJSON(col) = 1) constraint so only valid JSON is saved.
Producing JSON
FOR JSON PATH turns rows into a JSON array. Column aliases with dots create nested objects.
SELECT o.OrderId AS id,
o.OrderedAt AS [date],
c.Name AS [customer.name],
(SELECT ol.ProductId AS productId, ol.Quantity AS qty
FROM OrderLines ol WHERE ol.OrderId = o.OrderId
FOR JSON PATH) AS lines
FROM Orders o
JOIN Customers c ON c.CustomerId = o.CustomerId
WHERE o.OrderId = @OrderId
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;Reading JSON
JSON_VALUE reads one scalar value, JSON_QUERY reads an object or array, and OPENJSON turns an array into rows you can join or insert.
-- Attributes column: {"color":"blue","sizes":["S","M","L"]}
SELECT Name,
JSON_VALUE(Attributes, '$.color') AS Color,
JSON_QUERY(Attributes, '$.sizes') AS Sizes
FROM Products
WHERE ISJSON(Attributes) = 1;
-- A JSON array parameter into rows
SELECT *
FROM OPENJSON(@json)
WITH (ProductId int '$.productId', Qty int '$.qty');
-- Change one property
UPDATE Products SET Attributes = JSON_MODIFY(Attributes, '$.color', 'red')
WHERE ProductId = 7;Searching JSON fast
A WHERE on JSON_VALUE scans every row. Add a computed column for the property and index it, and SQL Server can seek.
ALTER TABLE Products ADD Color AS JSON_VALUE(Attributes, '$.color');
CREATE INDEX IX_Products_Color ON Products (Color);
SELECT ProductId, Name FROM Products WHERE Color = 'blue'; -- index seekInterview question
How do you make a search on a JSON property fast in SQL Server?
Add a computed column that extracts the property with JSON_VALUE and create an index on that column. Queries that filter on the computed column, or on the same JSON_VALUE expression, can then use an index seek instead of parsing the JSON in every row.
Want the full details? Read the official Microsoft documentation for this topic.