SQL Server JSON Path Expressions

SQL Server JSON path expressions select object properties and array elements. The same syntax appears in JSON_VALUE(), JSON_QUERY(), OPENJSON(), JSON_MODIFY(), JSON_PATH_EXISTS(), and JSON_CONTAINS(). See Microsoft’s JSON path expressions reference for the complete syntax and platform details.

Start at the document root

Every path begins with $, which represents the root value. Use a dot to select an object property and square brackets to select an array element. Array indexes start at zero:

DECLARE @doc nvarchar(max) = N'{"customer":{"name":"Ada"},"items":[{"sku":"A-1"},{"sku":"B-2"}]}';

SELECT
    JSON_VALUE(@doc, '$.customer.name') AS customer_name,
    JSON_VALUE(@doc, '$.items[0].sku') AS first_sku;

customer_name | first_sku
--------------|----------
Ada           | A-1

For the whole input document, $ is the root path. Nested paths combine object keys and array indexes, such as $.items[1].sku.

Quote property names with special characters

Use double quotes around a key name in the path when it contains spaces, dots, a dollar sign, or other special characters. The quotes belong inside the SQL string literal:

DECLARE @doc nvarchar(max) = N'{"customer data":{"$region.code":"eu-west"}}';

SELECT JSON_VALUE(@doc, '$."customer data"."$region.code"') AS region;

region
----------
eu-west

Without quotes, dots separate path steps. Quoting $region.code makes it one key instead of interpreting the dot as a nested property.

Choose lax or strict mode

Path mode is an optional prefix before $. lax is the default. When a requested value is missing or has the wrong type, lax mode generally returns an empty result or SQL NULL; strict mode generally raises an error. The exact behavior depends on the function: for example, JSON_MODIFY() in lax mode can insert a missing final property, while JSON_PATH_EXISTS() returns 0 for an absent path.

DECLARE @doc nvarchar(max) = N'{"customer":{"name":"Ada"}}';

SELECT JSON_VALUE(@doc, 'lax $.customer.phone') AS missing_in_lax_mode;

The query returns NULL. To require the path, use strict mode; this separate query raises an error because phone is missing:

DECLARE @doc nvarchar(max) = N'{"customer":{"name":"Ada"}}';

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

Consult each function’s guide for its specific lax/strict behavior.

Use array wildcards in SQL Server 2025

SQL Server 2025 (17.x) adds SQL/JSON wildcards, ranges, index lists, and the last token for array paths. This extended syntax requires the native json data type; it isn’t available for JSON stored in varchar or nvarchar, or on older SQL Server versions.

Use wildcard paths with functions that support the feature, including JSON_QUERY(), JSON_PATH_EXISTS(), and JSON_CONTAINS(). JSON_QUERY() needs WITH ARRAY WRAPPER to return multiple matches as an array:

DECLARE @doc json = N'{"items":[{"sku":"A-1"},{"sku":"B-2"}]}';

SELECT JSON_QUERY(@doc, '$.items[*].sku' WITH ARRAY WRAPPER) AS skus;
SELECT JSON_PATH_EXISTS(@doc, '$.items[*].sku') AS has_sku;
SELECT JSON_CONTAINS(@doc, N'B-2', '$.items[*].sku') AS has_b2;

skus
---------------
["A-1","B-2"]

has_sku
-------
1

has_b2
------
1

In JSON_VALUE(), a path that resolves to an object or array still returns NULL in lax mode because that function returns only scalars. See the JSON_QUERY(), JSON_PATH_EXISTS(), and JSON_CONTAINS() guides for their specific wildcard behavior.

Duplicate keys

When an object contains duplicate property names, JSON_VALUE() and JSON_QUERY() return the first value that matches the path. Use OPENJSON() when you need to enumerate matching keys and values instead of selecting one path result.

Browse the SQL Server JSON function guide for function syntax, version requirements, and task-based examples.