Menu

SQL Server JSON_VALUE(): Extract a Scalar from JSON

JSON_VALUE(expression, path) extracts one scalar value from a JSON document. Use it for strings, numbers, or Boolean values at a path. It does not return objects or arrays; use JSON_QUERY() for those. The function was introduced in SQL Server 2016 (13.x). See Microsoft’s JSON_VALUE reference.

Syntax

SQL Server 2022 (16.x) and earlier:

JSON_VALUE(expression, path)

SQL Server 2025 (17.x) and later also supports a RETURNING clause:

JSON_VALUE(expression, path [RETURNING data_type])

The RETURNING clause is available only when expression has SQL Server’s native json data type. With varchar or nvarchar JSON text, use TRY_CONVERT or CAST on the returned string when a typed SQL value is needed.

Read a property and filter rows

This example reads a string and a number from JSON text and uses a scalar value in a WHERE clause:

DECLARE @doc nvarchar(max) = N'{
  "orderId": 1042,
  "customer": { "name": "Ada Lovelace" },
  "status": "ready"
}';

SELECT
    JSON_VALUE(@doc, '$.orderId') AS order_id,
    JSON_VALUE(@doc, '$.customer.name') AS customer_name,
    JSON_VALUE(@doc, '$.status') AS status;

order_id | customer_name | status
---------|---------------|-------
1042     | Ada Lovelace  | ready

By default, the result is nvarchar(4000) and keeps the collation of the input expression. A numeric JSON value is returned as text, so convert it when you need numeric comparison or arithmetic:

SELECT EventId, Payload
FROM dbo.Events
WHERE TRY_CONVERT(int, JSON_VALUE(Payload, '$.orderId')) >= 1000;

Select object keys and array elements

Use dot notation for ordinary property names and array indexes in square brackets. Put property names containing spaces or special characters in double quotes inside the path:

DECLARE @doc nvarchar(max) = N'{
  "user name": "Ada",
  "tags": ["sql", "json"]
}';

SELECT
    JSON_VALUE(@doc, '$."user name"') AS user_name,
    JSON_VALUE(@doc, '$.tags[1]') AS second_tag;

user_name | second_tag
----------|-----------
Ada       | json

For the path syntax and escaping rules, see the SQL Server JSON path guide and Microsoft’s JSON path expressions.

Missing paths, objects, and arrays

The default path mode is lax. In this mode, a missing property or a path that points to an object or array returns SQL NULL. Prefix the path with strict when the path must exist and resolve to a scalar; a missing path or non-scalar value then raises an error.

DECLARE @doc nvarchar(max) = N'{"id": 7, "address": {"city": "Paris"}}';

SELECT
    JSON_VALUE(@doc, '$.missing') AS missing_in_lax_mode,
    JSON_VALUE(@doc, '$.address') AS object_in_lax_mode;

To require a path, run strict mode separately; a missing property or non-scalar value raises an error:

DECLARE @doc nvarchar(max) = N'{"id": 7, "address": {"city": "Paris"}}';

SELECT JSON_VALUE(@doc, 'strict $.missing');

To return the address object instead of NULL, use JSON_QUERY(@doc, '$.address'). To expand an array into rows, use OPENJSON().

Values longer than 4,000 characters

Without RETURNING, JSON_VALUE returns nvarchar(4000). If a scalar is longer than 4,000 characters, lax mode returns NULL and strict mode raises an error. To retrieve longer scalar text from JSON stored as varchar or nvarchar, use OPENJSON with an nvarchar(max) column definition:

DECLARE @longDoc nvarchar(max) = N'{"description":"' + REPLICATE(N'x', 4001) + N'"}';

SELECT description
FROM OPENJSON(@longDoc)
WITH (description nvarchar(max) '$.description');

SQL Server 2025 can instead return a wider SQL type with RETURNING, provided the input expression is the native json type:

DECLARE @doc json = N'{"orderId": 1042, "createdOn": "2026-10-02"}';

SELECT
    JSON_VALUE(@doc, '$.orderId' RETURNING int) AS order_id,
    JSON_VALUE(@doc, '$.createdOn' RETURNING date) AS created_on;

RETURNING also supports types such as bigint, decimal, float, varchar(max), nvarchar(max), time, and datetime2. Use a data type compatible with the JSON scalar you expect.

Use a scalar as a computed column

If a JSON property is frequently filtered or sorted, expose it as a computed column and consider indexing that column:

ALTER TABLE dbo.Events
ADD OrderId AS TRY_CONVERT(int, JSON_VALUE(Payload, '$.orderId'));

CREATE INDEX IX_Events_OrderId ON dbo.Events(OrderId);

Test the query plan and workload before adding the index. For guidance on matching computed-column definitions to query expressions, see Microsoft’s index JSON data.

For JSON objects and arrays, see JSON_QUERY(). To turn JSON collections into relational rows, use OPENJSON(). Browse the SQL Server JSON function guide for the full task-based list.

Advertisement