How to Parse JSON in SQL Server

How do I parse JSON in SQL Server?

OPENJSON lets you shred JSON text into relational rows and columns directly inside SQL Server.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use OPENJSON to parse JSON?

OPENJSON converts JSON arrays or objects into a tabular result set you can join, filter, and aggregate with regular T-SQL. It eliminates CLR code and speeds up ETL pipelines.

What is the basic OPENJSON syntax?

OPENJSON (json_expression [, path ]) WITH (schema_definition) returns one row per JSON element. The WITH clause casts JSON values to SQL data types and assigns column names.

How do I parse a JSON array stored in a column?

SELECT * FROM Orders CROSS APPLY OPENJSON(orders.json_payload) WITH (...column list…) as j; The CROSS APPLY feeds each row’s JSON into OPENJSON, expanding it on the fly.

Can I extract nested properties?

Yes. Specify a path argument or use dot-notation in the WITH clause. Example: product.price AS price money '$.details.price'.

Practical ecommerce example

The OrderItems table can hold an extra json_details nvarchar(max) column for tax and discount data. Use OPENJSON to materialize those values only when needed, keeping storage flexible.

Best practices for JSON parsing

1) Keep JSON columns nvarchar(max). 2) Create computed persisted columns for frequently queried keys. 3) Index computed columns, not raw JSON, for speed.

Common performance tips

Use the strict modifier (OPENJSON WITH … STRICT) to fail fast on bad JSON. Avoid SELECT *, project only required fields. Combine with APPLY instead of a subquery for readability.

Why How to Parse JSON in SQL Server is important

How to Parse JSON in SQL Server Example Usage


--Order payload sample: {"product_id":42,"qty":3,"sale_price":19.99}
SELECT o.id,
       j.product_id,
       j.quantity,
       j.price
FROM   Orders o
CROSS APPLY OPENJSON(o.order_payload) WITH (
    product_id int '$.product_id',
    quantity   int '$.qty',
    price      numeric(10,2) '$.sale_price'
) AS j
WHERE  j.quantity > 1;

How to Parse JSON in SQL Server Syntax


OPENJSON ( json_expression [ , path ] )
    [ WITH ( 
        column_name data_type [ '$.json_key' ] [ AS json_type ],
        ...
    ) ]

--Example in ecommerce context
SELECT o.id               AS order_id,
       c.name             AS customer_name,
       j.product_id,
       j.quantity,
       j.price
FROM   Orders      o
JOIN   Customers   c ON c.id = o.customer_id
CROSS APPLY OPENJSON(o.order_payload) WITH (
    product_id  int          '$.product_id',
    quantity    int          '$.qty',
    price       numeric(10,2) '$.sale_price'
) AS j;

Common Mistakes

Using VARCHAR instead of NVARCHAR for JSON text. VARCHAR can corrupt Unicode characters and breaks JSON validation. Always store JSON in NVARCHAR columns.
Selecting * from OPENJSON without a WITH clause. This returns key, value, type for every element and requires post-processing. Define a typed WITH clause to output clean columns.