Menu

SQL Server JSON_QUERY(): Extract JSON Objects and Arrays

JSON_QUERY(expression, path) extracts a JSON object or array from a JSON document. Use JSON_VALUE() for a scalar value such as a string or number. JSON_QUERY() returns an nvarchar(max) JSON fragment and preserves the collation of the input expression. The function was introduced in SQL Server 2016 (13.x). See Microsoft’s JSON_QUERY reference.

Syntax

JSON_QUERY(expression [ , path ])

If path is omitted, it defaults to $ and the function returns the input expression. SQL Server 2017 (14.x) and Azure SQL Database also allow a variable for path.

Extract an object or array

This example extracts a nested object and an array from JSON text:

DECLARE @doc nvarchar(max) = N'{"customer":{"name":"Ada Lovelace","tier":"gold"},"tags":["sql","json"],"status":"ready"}';

SELECT
    JSON_QUERY(@doc, '$.customer') AS customer,
    JSON_QUERY(@doc, '$.tags') AS tags;

customer                              | tags
--------------------------------------|-----------------
{"name":"Ada Lovelace","tier":"gold"} | ["sql","json"]

The path must resolve to an object or array. For $.status, which points to a scalar string, lax mode returns SQL NULL; strict mode raises an error. Use JSON_VALUE(@doc, '$.status') to return that scalar.

Lax and strict path modes

lax is the default. A missing path or a path that resolves to a scalar returns SQL NULL in lax mode. Prefix the path with strict to require an object or array at that path; a missing value or wrong JSON type raises an error.

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

SELECT
    JSON_QUERY(@doc, '$.missing') AS missing_in_lax_mode,
    JSON_QUERY(@doc, '$.name') AS scalar_in_lax_mode,
    JSON_QUERY(@doc, '$.address') AS object_in_lax_mode;

The first two columns are SQL NULL; the third returns the address object. Run a strict path separately when demonstrating its error behavior:

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

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

If the input contains malformed JSON, JSON_QUERY() can raise an error while scanning the document. It does not serve as a substitute for validating arbitrary input.

Include a JSON fragment in FOR JSON output

SQL Server treats ordinary text as a string when producing FOR JSON output. Wrap text that already contains a JSON object or array with JSON_QUERY() so that FOR JSON includes it as a JSON fragment instead of escaping it as a quoted string:

SELECT
    1 AS event_id,
    JSON_QUERY(N'{"source":"api","retry":false}') AS metadata
FOR JSON PATH;

[{"event_id":1,"metadata":{"source":"api","retry":false}}]

This pattern is useful for JSON text produced by a subquery or stored in a character column. Make sure the text is valid JSON before using it as a fragment.

Select multiple values from an array in SQL Server 2025

SQL Server 2025 (17.x) adds SQL/JSON array wildcards and the WITH ARRAY WRAPPER clause as generally available features. The clause requires the native json data type as input; it is unavailable for varchar or nvarchar JSON text and older SQL Server versions. Microsoft’s SQL Server JSON overview lists both features as generally available, and the SQL Server 2025 release notes say features outside the preview list match the release status.

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

SELECT JSON_QUERY(@doc, '$.items[*].sku' WITH ARRAY WRAPPER) AS skus;

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

The wrapper is required because a wildcard can match more than one value and JSON_QUERY() returns those matches as a JSON array. For wildcard syntax and other path rules, see the SQL Server JSON path guide. For current native storage limits and availability, see the SQL Server json data type reference.

For scalar extraction, see JSON_VALUE(). To expand JSON arrays into relational rows, use OPENJSON(). Browse the SQL Server JSON function guide for the full task-based list.

Advertisement