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
