Compartir a través de

JSON_QUERY (Transact-SQL)

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPunto de conexión de análisis SQL en Microsoft FabricAlmacenamiento de Microsoft Fabric

Extrae un objeto o una matriz desde una cadena JSON.

Para extraer un valor escalar de una cadena JSON en lugar de un objeto o una matriz, consulte JSON_VALUE. Para información sobre las diferencias entre JSON_VALUE y JSON_QUERY, consulte Comparación de JSON_VALUE y JSON_QUERY.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

expresión

Expresión. Suele ser el nombre de una variable o una columna con texto JSON.

Si JSON_QUERY encuentra JSON que no es válido en la expresión antes de encontrar el valor identificado por ruta de acceso, la función devuelve un error. Si JSON_QUERY no encuentra el valor identificado por ruta de acceso, examina todo el texto y devuelve un error si encuentra JSON que no es válido en ninguna parte de la expresión.

ruta

Ruta de acceso JSON que especifica el objeto o la matriz que se va a extraer.

En SQL Server 2017 (14.x) y en Azure SQL Database, puede proporcionar una variable como el valor de path.

La ruta de acceso JSON puede especificar el modo lax o strict para el análisis. Si no se especifica el modo de análisis, el modo lax es el valor predeterminado. Para más información, vea Expresiones de ruta de acceso JSON (SQL Server).

El valor predeterminado de path es $. Como resultado, si no se proporciona un valor para path, JSON_QUERY devuelve la expresión de entrada.

Si el formato de path no es válido, JSON_QUERY devuelve un error.

CON CONTENEDOR DE MATRIZ

Nota:

WITH ARRAY WRAPPER actualmente está en versión preliminar y solo está disponible en la versión preliminar de SQL Server 2025 (17.x).

La función SQL ANSI JSON_QUERY se usa actualmente para devolver un objeto JSON o una matriz en una ruta de acceso especificada. Con la compatibilidad con caracteres comodín de matriz en la expresión de ruta de acceso SQL/JSON introducida en la versión preliminar JSON_QUERY de SQL Server 2025 (17.x), se puede usar para devolver propiedades especificadas de elementos en una matriz JSON donde cada elemento es un objeto JSON. Dado que las búsquedas con caracteres comodín pueden devolver varios valores, especifique la WITH ARRAY WRAPPER cláusula en una expresión de consulta JSON junto con una expresión de ruta de acceso SQL/JSON con caracteres comodín o rango o lista para devolver los valores como una matriz JSON. WITH ARRAY WRAPPER la cláusula solo se admite si la entrada es un tipo json .

Tenga en cuenta el siguiente documento JSON:

declare @j JSON = '{
    "id": 2,
    "first_name": "Mamie",
    "last_name": "Baudassi",
    "email": "mbaudassi1@abc.net.au",
    "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"
        }
    ]
}';

La ruta de acceso $.credit_cards apunta a una matriz JSON donde cada elemento es un objeto JSON válido. Ahora, la JSON_QUERY función se puede usar con compatibilidad con caracteres comodín de matriz para devolver todos los valores específicos de la type propiedad como:

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

En la tabla siguiente se muestran varios ejemplos de expresión de ruta de acceso SQL/JSON con comodín y el valor devuelto mediante JSON_QUERY WITH ARRAY WRAPPER.

Ruta Valor devuelto
$.creditcards[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"]

Valor devuelto

Devuelve un fragmento de JSON de tipo nvarchar(max). La intercalación del valor devuelto es la misma que la intercalación de la expresión de entrada.

Si el valor no es un objeto o una matriz:

  • En el modo lax, JSON_QUERY devuelve null.

  • En el modo strict, JSON_QUERY devuelve un error.

Comentarios

Modo lax y modo strict

Observe el siguiente texto JSON:

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

En la tabla siguiente se compara el comportamiento de JSON_QUERY en modo lax y modo strict. Para más información sobre la especificación del modo de ruta de acceso opcional (lax o strict), vea Expresiones de ruta de acceso JSON (SQL Server).

Ruta Valor devuelto en el modo lax Valor devuelto en el modo strict Más información
$ Devuelve todo el texto JSON. Devuelve todo el texto JSON.
$.info.type NULL Error No es un objeto o una matriz.

En su lugar, use JSON_VALUE.
$.info.address.town NULL Error No es un objeto o una matriz.

En su lugar, use JSON_VALUE.
$.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 No es una matriz.
$.info.none NULL Error La propiedad no existe.

Uso de JSON_QUERY con FOR JSON

JSON_QUERY devuelve un fragmento de JSON válido. Como resultado, FOR JSON no escapa caracteres especiales en el valor devuelto JSON_QUERY.

Si se van a devolver resultados con FOR JSON y se van a incluir datos que ya están en formato JSON (en una columna o como resultado de una expresión), encapsule los datos JSON con JSON_QUERY sin el parámetro path.

Ejemplos

Un. Devolver un fragmento JSON

En el ejemplo siguiente se muestra cómo devolver un fragmento de JSON de una columna CustomFields en los resultados de la consulta.

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

B. Incluir fragmentos JSON en la salida FOR JSON

En el ejemplo siguiente se muestra cómo incluir fragmentos de JSON en la salida de la cláusula FOR JSON.

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

C. Uso de WITH ARRAY WRAPPER con JSON_QUERY función

En el ejemplo siguiente se muestra el uso de WITH ARRAY WRAPPER con la JSON_QUERY función para devolver varios elementos de una matriz JSON:

DECLARE @j JSON = ' {"id":2,"first_name":"Mamie","last_name":"Baudassi","email":"mbaudassi1@abc.net.au","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;

Este es el conjunto de resultados.

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

Contenido relacionado