Skip to content

json-query-transact-sql.md

Latest commit

 

History

History
223 lines (170 loc) · 9.3 KB

json-query-transact-sql.md

File metadata and controls

223 lines (170 loc) · 9.3 KB
title JSON_QUERY (Transact-SQL)
description JSON_QUERY extracts an object or an array from a JSON string.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer jovanpop, umajay, randolphwest
ms.date 10/27/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
JSON_QUERY
JSON_QUERY_TSQL
helpviewer_keywords
JSON, extracting
JSON, querying
JSON_QUERY function
dev_langs
TSQL
monikerRange =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

JSON_QUERY (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-fabricse-fabricdw-fabricsqldb]

The JSON_QUERY syntax extracts an object or an array from a JSON string.

To extract a scalar value from a JSON string instead of an object or an array, see JSON_VALUE. For info about the differences between JSON_VALUE and JSON_QUERY, see Compare JSON_VALUE and JSON_QUERY.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

JSON_QUERY ( expression [ , path ] [ WITH ARRAY WRAPPER ] )

Arguments

expression

An expression. Typically the name of a variable or a column that contains JSON text.

If JSON_QUERY finds JSON that isn't valid in expression before it finds the value identified by path, the function returns an error. If JSON_QUERY doesn't find the value identified by path, it scans the entire text and returns an error if it finds JSON that isn't valid anywhere in expression.

path

A JSON path that specifies the object or the array to extract.

In [!INCLUDE sssql17-md] and in [!INCLUDE ssazure-sqldb], you can provide a variable as the value of path.

The JSON path can specify lax or strict mode for parsing. If you don't specify the parsing mode, lax mode is the default. For more info, see JSON Path Expressions in the SQL Database Engine.

The default value for path is $. As a result, if you don't provide a value for path, JSON_QUERY returns the input expression.

If the format of path isn't valid, JSON_QUERY returns an error.

WITH ARRAY WRAPPER

Note

WITH ARRAY WRAPPER is currently in preview and only available in [!INCLUDE sssql25-md].

The ANSI SQL JSON_QUERY function is currently used to return a JSON object or array in a specified path. With the support for array wildcards in SQL/JSON path expression introduced in [!INCLUDE sssql25-md], JSON_QUERY can be used to return specified properties of elements in a JSON array where each element is a JSON object. Since wildcard searches can return multiple values, specify the WITH ARRAY WRAPPER clause in a JSON query expression along with a SQL/JSON path expression with wildcard or range or list to return the values as a JSON array. WITH ARRAY WRAPPER clause is supported only if the input is a json type.

Consider the following JSON document:

DECLARE @j AS JSON = '{
    "id": 2,
    "first_name": "Mamie",
    "last_name": "Baudassi",
    "email": "mbaudassi1@example.com",
    "gender": "Female",
    "ip_address": "148.199.129.123",
    "credit_cards": [
        {
            "type": "jcb",
            "card#": "3545138777072343",
            "currency": "Koruna"
        },
        {
            "type": "diners-club-carte-blanche",
            "card#": "30282304348533",
            "currency": "Dong"
        },
        {
            "type": "jcb",
            "card#": "3585303288595361",
            "currency": "Yuan Renminbi"
        },
        {
            "type": "maestro",
            "card#": "675984450768756054",
            "currency": "Rupiah"
        },
        {
            "type": "instapayment",
            "card#": "6397068371771473",
            "currency": "Euro"
        }
    ]
}';

The path $.credit_cards points to a JSON array where each element is a valid JSON object. Now, the JSON_QUERY function can be used with array wildcard support to return all or specific values of the type property like:

SELECT JSON_QUERY(@j, '$.credit_cards[*].type' WITH ARRAY WRAPPER);

The following table shows various examples of SQL/JSON path expression with wildcard and the return value using JSON_QUERY WITH ARRAY WRAPPER.

Path Return value
$.credit_cards[0].type ["jcb"]
$.credit_cards[*].type ["jcb","diners-club-carte-blanche","jcb","maestro","instapayment"]
$.credit_cards[0, 2].type ["jcb","jcb"]
$.credit_cards[1 to 3].type ["diners-club-carte-blanche","jcb","maestro"]
$.credit_cards[last].type ["instapayment"]
$.credit_cards[last, 0].type ["instapayment","jcb"]
$.credit_cards[last, last].type ["instapayment","instapayment"]
$.credit_cards[ 0, 2, 4].type ["jcb","jcb","instapayment"]

Return value

Returns a JSON fragment of type nvarchar(max). The collation of the returned value is the same as the collation of the input expression.

If the value isn't an object or an array:

  • In lax mode, JSON_QUERY returns null.

  • In strict mode, JSON_QUERY returns an error.

Remarks

Lax mode and strict mode

Consider the following JSON text:

{
   "info": {
      "type": 1,
      "address": {
         "town": "Cheltenham",
         "county": "Gloucestershire",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}

The following table compares the behavior of JSON_QUERY in lax mode and in strict mode. For more info about the optional path mode specification (lax or strict), see JSON Path Expressions in the SQL Database Engine.

Path Return value in lax mode Return value in strict mode More info
$ Returns the entire JSON text. Returns the entire JSON text.
$.info.type NULL Error Not an object or array.

Use JSON_VALUE instead.
$.info.address.town NULL Error Not an object or array.

Use JSON_VALUE instead.
$.info."address" N'{ "town":"Cheltenham", "county":"Gloucestershire", "country":"England" }' N'{ "town":"Cheltenham", "county":"Gloucestershire", "country":"England" }'
$.info.tags N'[ "Sport", "Water polo"]' N'[ "Sport", "Water polo"]'
$.info.type[0] NULL Error Not an array.
$.info.none NULL Error Property doesn't exist.

Use JSON_QUERY with FOR JSON

JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON doesn't escape special characters in the JSON_QUERY return value.

If you're returning results with FOR JSON, and you're including data that's already in JSON format (in a column or as the result of an expression), wrap the JSON data with JSON_QUERY without the path parameter.

Examples

A. Return a JSON fragment

The following example shows how to return a JSON fragment from a CustomFields column in query results.

SELECT PersonID,
       FullName,
       JSON_QUERY(CustomFields, '$.OtherLanguages') AS Languages
FROM Application.People;

B. Include JSON fragments in FOR JSON output

The following example shows how to include JSON fragments in the output of the FOR JSON clause.

SELECT StockItemID,
       StockItemName,
       JSON_QUERY(Tags) AS Tags,
       JSON_QUERY(CONCAT('["', ValidFrom, '","', ValidTo, '"]')) AS ValidityPeriod
FROM Warehouse.StockItems
FOR JSON PATH;

C. Use WITH ARRAY WRAPPER with JSON_QUERY function

The following example shows the use of WITH ARRAY WRAPPER with the JSON_QUERY function to return multiple elements from a JSON array:

DECLARE @j JSON = '
{"id":2, "first_name":"Mamie", "last_name":"Baudassi", "email":"mbaudassi1@example.com", "gender":"Female", "ip_address":"148.199.129.123", "credit_cards":[ {"type":"jcb", "card#":"3545138777072343", "currency":"Koruna"}, {"type":"diners-club-carte-blanche", "card#":"30282304348533", "currency":"Dong"}, {"type":"jcb", "card#":"3585303288595361", "currency":"Yuan Renminbi"}, {"type":"maestro", "card#":"675984450768756054", "currency":"Rupiah"}, {"type":"instapayment", "card#":"6397068371771473", "currency":"Euro"}]}
';
SELECT JSON_QUERY(@j, '$.credit_cards[*].type' WITH ARRAY WRAPPER ) as credit_card_types;

[!INCLUDE ssresult-md]

credit_card_types
--------
["jcb","diners-club-carte-blanche","jcb","maestro","instapayment"]

Related content