SQL Server 2025 (preview) working with JSON

 

Working with JSON in SQL Server

SQL Server allows you to store JSON text in database tables and use built-in functions to query or modify it using standard T-SQL.

1. Extract Values from JSON

You can extract values using:

JSON_VALUE: Returns a scalar value (e.g., string, number).

JSON_QUERY: Returns an object or array.

E.g.

SELECT JSON_VALUE(jsonCol, '$.info.address.Town') AS Town

FROM People

WHERE ISJSON(jsonCol) > 0;

SELECT JSON_QUERY(jsonCol, '$.info.skills') AS Skills

FROM People;

2. Modify JSON Data

Use JSON_MODIFY to update parts of a JSON string.

E.g

DECLARE @json NVARCHAR(MAX);

SET @json = '{"city":"Paris"}';

SET @json = JSON_MODIFY(@json, '$.city', 'London');

SELECT @json AS ModifiedJson;

-- Result: {"city":"London"}

3. Convert JSON to Rowset

Use OPENJSON to transform JSON arrays into tabular data.

E.g

DECLARE @json NVARCHAR(MAX) = N'[

  {"id": 1, "name": "Alice"},

  {"id": 2, "name": "Bob"}

]';

SELECT *

FROM OPENJSON(@json)

WITH (

    id INT,

    name NVARCHAR(100)

);

4. Validate JSON

E.g

Checks if a string is valid JSON (returns 1 if true).

SELECT ISJSON('{"name":"value"}') AS IsJson; -- Returns 1

SELECT ISJSON('invalid') AS IsJson;          -- Returns 0

5. Combined JSON

SELECT 

  Name,

  JSON_VALUE(jsonCol, '$.info.address.Town') AS Town,

  JSON_QUERY(jsonCol, '$.info.skills') AS Skills

FROM People

WHERE ISJSON(jsonCol) = 1

  AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade';

Cheers

Samitha