SQL JSON Functions

This document provides comprehensive details on the JSON functions available in SQL Server, enabling you to work with JSON data directly within your SQL queries.

Introduction to JSON in SQL Server

SQL Server provides built-in support for JSON, allowing you to store, query, and manipulate JSON data efficiently. This is particularly useful for applications that exchange data in JSON format or when dealing with semi-structured data.

Key JSON Functions

SQL Server offers several functions to facilitate JSON processing:

Function Name Description Syntax Example
JSON_VALUE Extracts a scalar value from a JSON string. JSON_VALUE(expression, path)
JSON_QUERY Extracts an object or an array from a JSON string. JSON_QUERY(expression, path)
ISJSON Checks if a string contains valid JSON. ISJSON(expression)
JSON_MODIFY Updates a value in a JSON string or inserts a new key-value pair. JSON_MODIFY(expression, path, newValue)
OPENJSON Parses JSON text and returns objects and properties from the JSON input as rows and columns. OPENJSON(jsonExpression, [path])

JSON_VALUE

The JSON_VALUE function is used to extract a scalar value (string, number, boolean, null) from a JSON string. You specify the JSON string and a path to the value you want to extract.

SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.name');

This query would return: Alice

JSON_QUERY

Use JSON_QUERY to extract a JSON object or an array from a JSON string. It returns the JSON fragment as a string.

SELECT JSON_QUERY('{"person": {"name": "Bob", "city": "New York"}}', '$.person');

This query would return: {"name": "Bob", "city": "New York"}

ISJSON

The ISJSON function is a simple predicate that returns 1 (true) if the input string is valid JSON and 0 (false) otherwise.

SELECT ISJSON('{"id": 1, "status": "active"}');

This query would return: 1

JSON_MODIFY

JSON_MODIFY allows you to update values within a JSON document. You can change existing values, add new key-value pairs, or even remove elements.

SELECT JSON_MODIFY('{"name": "Charlie", "score": 85}', '$.score', 90);

This query would return: {"name": "Charlie", "score": 90}

OPENJSON

OPENJSON is a powerful table-valued function that transforms JSON into relational format (rows and columns). This is essential for querying JSON data using standard SQL syntax.


DECLARE @json NVARCHAR(MAX) = N'[
    {"product": "Laptop", "price": 1200},
    {"product": "Mouse", "price": 25}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    ProductName NVARCHAR(50) '$.product',
    ProductPrice DECIMAL(10, 2) '$.price'
);

This query would return a table with two rows, each containing product name and price.

Further Reading