SQL Server › JSON Support in SQL Server

How do you convert a JSON array stored in a column into a relational table format that can be queried like normal rows?

Intermediate

Answer

You use the OPENJSON function, which parses a JSON array or object and returns its elements as a set of rows and columns, letting you treat data that was originally stored as JSON text just like any other table when writing joins, filters, or aggregations.

SELECT o.OrderId, items.ProductName, items.Quantity
FROM Orders o
CROSS APPLY OPENJSON(o.ItemsJson)
WITH (
  ProductName NVARCHAR(100) '$.name',
  Quantity INT '$.quantity'
) AS items;

Real-world example An order system stores each order's line items as a JSON array, then uses OPENJSON with CROSS APPLY to break that array into individual rows for reporting, treating what was originally flexible JSON data just like a normal relational table.

Common follow-ups: What is the purpose of the WITH clause when using OPENJSON?;Can OPENJSON handle deeply nested JSON structures?

Joins;Subqueries