SQL for .NET developers Intermediate

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.

JSON functions at a glance
  1. FOR JSON PATHRows to JSON text
  2. JSON_VALUERead one value, such as $.color
  3. JSON_QUERYRead an object or array
  4. OPENJSONJSON array to rows
  5. JSON_MODIFYChange one property
  6. 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.

SQL
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.

SQL
-- 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.

SQL
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 seek

Interview 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.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.