JSON_VALUE function

The JSON_VALUE function in SQL Server is a built-in function introduced in SQL Server 2016 to extract a scalar value from a JSON string. It is particularly useful when working with JSON data stored in database tables, allowing developers to query and manipulate JSON content directly in T-SQL.

Syntax

JSON_VALUE ( expression, path )

expression: The JSON string from which the value is to be extracted.

path: The JSON path that specifies the key or property to retrieve. It uses the dot-notation or bracket-notation JSON syntax.

Key Features

Single Scalar Value Extraction:

JSON_VALUE returns only scalar values (e.g., string, number, Boolean, or null).
It does not return complex types such as objects or arrays.

Data Type of Return Value:

The return type is nvarchar(4000).
If the value exceeds 4000 characters, the function returns NULL.

Error Handling:

If the JSON path is invalid, JSON_VALUE returns NULL.
If the input string is not a valid JSON or does not conform to the JSON structure, the function returns NULL.

Examples

Basic Usage

DECLARE @json NVARCHAR(MAX) = N'{
    "name": "John Doe",
    "age": 30,
    "address": {
        "city": "New York",
        "zip": "10001"
    }
}';

-- Extracting simple properties
SELECT 
    JSON_VALUE(@json, '$.name') AS Name, -- Returns 'John Doe'
    JSON_VALUE(@json, '$.age') AS Age;  -- Returns '30'

Extracting Nested Properties

SELECT 
    JSON_VALUE(@json, '$.address.city') AS City, -- Returns 'New York'
    JSON_VALUE(@json, '$.address.zip') AS Zip;  -- Returns '10001'

Using in a Query

Assume a table Orders has a column OrderDetails storing JSON data:

SELECT 
    OrderId,
    JSON_VALUE(OrderDetails, '$.CustomerName') AS CustomerName,
    JSON_VALUE(OrderDetails, '$.TotalAmount') AS TotalAmount
FROM Orders
WHERE JSON_VALUE(OrderDetails, '$.Status') = 'Shipped';

Limitations

Non-Scalar Values: JSON_VALUE cannot extract objects or arrays. Use JSON_QUERY for such requirements.

Length Limit: The extracted value cannot exceed 4000 characters. For larger values, consider alternate methods.

Use Cases

Extracting Specific Data: Fetching individual fields from JSON stored in a database.

Filtering and Searching: Using JSON fields in WHERE or JOIN clauses.

Integrating JSON with Relational Data: Combining relational and JSON data seamlessly in queries.

By leveraging JSON_VALUE, SQL Server simplifies handling JSON data, enabling developers to process semi-structured data without additional application logic.