MS SQL Server JSON Basics Cheat Sheet
This cheat sheet provides a quick reference for common functions
and syntax used to work with JSON data in Microsoft SQL Server (SQL Server 2016
and later).
1. Validating JSON Text
Use ISJSON() to check if a string contains valid JSON. It
returns 1 for valid JSON, 0 for invalid JSON, and NULL if the input is NULL.
-- Example Data
DECLARE @json_valid NVARCHAR(MAX) = N'{"name": "John Doe",
"age": 30}';
DECLARE @json_invalid NVARCHAR(MAX) = N'{"name": "Jane
Doe", "age": }'; -- Invalid JSON
-- Check Validity
SELECT ISJSON(@json_valid) AS IsValid1;
-- Returns 1
SELECT ISJSON(@json_invalid) AS IsValid2;
-- Returns 0
SELECT ISJSON(NULL) AS IsNullValid;
-- Returns NULL
2. Extracting Scalar Values
Use JSON_VALUE() to extract a single scalar value (string,
number, boolean) from a JSON string using a JSON path expression.
●
Path Syntax: $ represents the root
object. Use . for member access and [index] for array element access (0-based).
-- Example Data
DECLARE @json_data NVARCHAR(MAX) = N'{
"person": {
"name": "Alice",
"age": 28,
"isStudent": false,
"address": {
"street": "123 Main
St",
"city":
"Anytown"
},
"courses":
["Math", "History"]
}
}';
-- Extract Scalar Values
SELECT
JSON_VALUE(@json_data,
'$.person.name') AS PersonName, --
Returns 'Alice'
JSON_VALUE(@json_data,
'$.person.age') AS PersonAge, --
Returns '28' (as string by default)
JSON_VALUE(@json_data,
'$.person.isStudent') AS IsStudent, --
Returns 'false'
JSON_VALUE(@json_data,
'$.person.address.city') AS City, --
Returns 'Anytown'
JSON_VALUE(@json_data,
'$.person.courses[0]') AS FirstCourse; -- Returns 'Math'
-- Extract with specific data type (using CAST/CONVERT)
SELECT
CAST(JSON_VALUE(@json_data,
'$.person.age') AS INT) AS PersonAgeInt,
CAST(JSON_VALUE(@json_data,
'$.person.isStudent') AS BIT) AS IsStudentBit;
3. Extracting Objects or Arrays
Use JSON_QUERY() to extract an object or an array from a JSON
string. JSON_VALUE returns NULL if it targets an object or array.
-- Example Data (using @json_data from above)
-- Extract Object
SELECT JSON_QUERY(@json_data, '$.person.address') AS AddressObject;
-- Returns: {"street": "123 Main St", "city":
"Anytown"}
-- Extract Array
SELECT JSON_QUERY(@json_data, '$.person.courses') AS CoursesArray;
-- Returns: ["Math", "History"]
-- Extracting an object/array with JSON_VALUE returns NULL
SELECT JSON_VALUE(@json_data, '$.person.address') AS AddressValueIsNull; --
Returns NULL
4. Modifying JSON Data
Use JSON_MODIFY() to update a property value, insert a new
property, or delete a property within a JSON string. It returns the modified JSON string.
●
Path Syntax: Same as JSON_VALUE and
JSON_QUERY.
●
append keyword: Adds an element to the
end of an array.
●
lax mode (default): If the path doesn't
exist for an update/delete, it doesn't raise an error.
●
strict mode: If the path doesn't
exist for an update/delete, it raises an error.
-- Example Data
DECLARE @json_doc NVARCHAR(MAX) = N'{"name": "Bob",
"age": 35, "skills": ["SQL"]}';
-- Update existing value
SET @json_doc = JSON_MODIFY(@json_doc, '$.age', 36);
-- @json_doc is now: {"name": "Bob", "age": 36,
"skills": ["SQL"]}
-- Insert new key/value pair
SET @json_doc = JSON_MODIFY(@json_doc, '$.city', 'New York');
-- @json_doc is now: {"name": "Bob", "age": 36,
"skills": ["SQL"], "city": "New York"}
-- Append to an array
SET @json_doc = JSON_MODIFY(@json_doc, 'append $.skills', 'JSON');
-- @json_doc is now: {"name": "Bob", "age": 36,
"skills": ["SQL", "JSON"], "city":
"New York"}
-- Delete a key/value pair (set value to NULL)
SET @json_doc = JSON_MODIFY(@json_doc, '$.city', NULL);
-- @json_doc is now: {"name": "Bob", "age": 36,
"skills": ["SQL", "JSON"]}
SELECT @json_doc AS ModifiedJson;
5. Parsing JSON into Rows and Columns
Use OPENJSON() to parse JSON text and return objects and
properties as rows and columns. It's often used with a WITH clause to define
the output schema.
●
Default Schema: Returns key/value pairs
and type information.
●
Explicit Schema (WITH clause):
Defines columns and their data types, mapping them to JSON properties using
JSON paths.
-- Example Data
DECLARE @json_array NVARCHAR(MAX) = N'[
{"id": 1, "name":
"Product A", "price": 10.50},
{"id": 2, "name":
"Product B", "price": 25.00, "tags":
["tech", "gadget"]},
{"id": 3, "name":
"Product C", "price": 5.75}
]';
-- Using OPENJSON with Default Schema
SELECT [key], [value], [type]
FROM OPENJSON(@json_array);
/* Returns rows representing each object in the array:
key | value
| type
----|-----------------------------------------------------------|------
0 | {"id": 1,
"name": "Product A", "price": 10.50} | 5 (Object)
1 | {"id": 2,
"name": "Product B", "price": 25.00,
"tags":...}| 5 (Object)
2 | {"id": 3,
"name": "Product C", "price": 5.75} | 5 (Object)
*/
-- Using OPENJSON with Explicit Schema (WITH clause)
SELECT *
FROM OPENJSON(@json_array)
WITH (
ProductID INT '$.id',
ProductName VARCHAR(100) '$.name',
Price DECIMAL(10, 2) '$.price',
Tags NVARCHAR(MAX) '$.tags' AS JSON
-- Keep nested array as JSON
);
/* Returns:
ProductID | ProductName | Price | Tags
----------|-------------|-------|---------------------
1 | Product A | 10.50 | NULL
2 | Product B | 25.00 | ["tech",
"gadget"]
3 | Product C | 5.75
| NULL
*/
-- Accessing nested arrays within OPENJSON
DECLARE @json_single_obj NVARCHAR(MAX) = N'{
"orderId": 101,
"customer": "Jane
Smith",
"items": [
{"sku": "X1",
"qty": 2},
{"sku": "Y2",
"qty": 1}
]
}';
SELECT i.*
FROM OPENJSON(@json_single_obj, '$.items') -- Specify the path to the array
WITH (
SKU VARCHAR(10) '$.sku',
Quantity INT '$.qty'
) AS i;
/* Returns:
SKU | Quantity
----|----------
X1 | 2
Y2 | 1
*/
6. Formatting T-SQL Results as JSON
Use FOR JSON to format query results as JSON text.
●
FOR JSON PATH: Gives you full control
over the output format using dot notation in column aliases. Nested structures
are created based on these aliases.
●
FOR JSON AUTO: Automatically creates
nested JSON structures based on the table structure in the SELECT statement and
the order of columns.
-- Example Table Data
(Assume a table named 'Employees')
-- EmployeeID | FirstName | LastName | Department | ManagerID
-- -----------|-----------|----------|------------|-----------
-- 1 | John | Doe
| Sales | NULL
-- 2 | Jane | Smith
| HR | 1
-- 3 | Peter | Jones
| Sales | 1
-- Using FOR JSON PATH
SELECT
EmployeeID AS 'employee.id',
FirstName AS 'employee.firstName',
LastName AS 'employee.lastName',
Department AS 'employee.dept'
FROM Employees
WHERE EmployeeID = 1
FOR JSON PATH, ROOT('employees'); -- ROOT adds a root element
-- Returns: {"employees":[{"employee":{"id":1,"firstName":"John","lastName":"Doe","dept":"Sales"}}]}
-- Using FOR JSON AUTO
SELECT
EmployeeID,
FirstName,
LastName,
Department
FROM Employees
WHERE Department = 'Sales'
FOR JSON AUTO;
-- Returns:
[{"EmployeeID":1,"FirstName":"John","LastName":"Doe","Department":"Sales"},{"EmployeeID":3,"FirstName":"Peter","LastName":"Jones","Department":"Sales"}]
-- FOR JSON PATH with Nested Structure
SELECT
e.EmployeeID AS 'id',
e.FirstName AS 'name.first',
e.LastName AS 'name.last',
(SELECT m.FirstName + ' ' +
m.LastName
FROM Employees m
WHERE m.EmployeeID = e.ManagerID) AS
'managerName'
FROM Employees e
WHERE e.EmployeeID = 2
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER; -- WITHOUT_ARRAY_WRAPPER removes the
outer [] for single results
-- Returns:
{"id":2,"name":{"first":"Jane","last":"Smith"},"managerName":"John
Doe"}
This cheat sheet covers the fundamental JSON functions in SQL
Server. Remember to consult the official Microsoft documentation for more
advanced features and options.
