Querying JSON in SQL Server

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Create JSON from a product row and read a property with JSON_VALUE.

Run the example

SELECT p.ProductID, JSON_VALUE(j.Doc, '$.product.name') AS ProductName
FROM dbo.Products AS p
CROSS APPLY (
    SELECT p.ProductName AS [product.name]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS j(Doc)
WHERE p.ProductID BETWEEN 1 AND 2
ORDER BY p.ProductID;

Read it step by step

1. FOR JSON PATH builds a small JSON document from each canonical product name.
2. JSON_VALUE reads the scalar property at $.product.name.
3. The outer ORDER BY makes the two-row display repeatable; SQL Server 2019 stores this JSON as text, not a native JSON column type.

Check the result

The round trip demonstrates path extraction without changing product data.

Practice

Extract a product name through JSON for ProductID 3.

SELECT p.ProductID, JSON_VALUE(j.Doc, '$.product.name') AS ProductName
FROM dbo.Products AS p
CROSS APPLY (
    SELECT p.ProductName AS [product.name]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS j(Doc)
WHERE p.ProductID = 3;