JSON_QUERY (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores do Banco de Dados SQL doAzure Azure Instância Gerenciada de SQLdo Azure Synapse Analytics ponto deextremidade de análise de SQL no Microsoft Fabric Warehouse no Banco de Dados SQLdo Microsoft Fabricno Microsoft Fabric

A JSON_QUERY sintaxe extrai um objeto ou uma matriz de uma cadeia de caracteres JSON.

Para extrair um valor escalar de uma cadeia de caracteres JSON em vez de um objeto ou uma matriz, consulte JSON_VALUE. Para obter informações sobre as diferenças entre JSON_VALUE e JSON_QUERY, consulte Comparar JSON_VALUE e JSON_QUERY.

Convenções de sintaxe de Transact-SQL

Syntax

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

Arguments

expression

Uma expressão. Normalmente, o nome de uma variável ou de uma coluna que contém o texto JSON.

Se JSON_QUERY encontrar JSON que não seja válido na expressão antes de localizar o valor identificado pelo caminho, a função retornará um erro. Se JSON_QUERY não encontrar o valor identificado por caminho, ele examinará todo o texto e retornará um erro se encontrar JSON que não seja válido em qualquer lugar na expressão.

path

Um demarcador JSON que especifica o objeto ou a matriz a ser extraída.

No SQL Server 2017 (14.x) e no Banco de Dados SQL do Azure, você pode fornecer uma variável como o valor do caminho.

O demarcador JSON pode especificar o modo incerto ou estrito para análise. Se você não especificar o modo de análise, o modo incerto será o padrão. Para obter mais informações, consulte Expressões de caminho JSON no Mecanismo de Banco de Dados SQL.

O valor padrão do caminho é $. Como resultado, se você não fornecer um valor para o caminho, JSON_QUERY retornará a expressão de entrada.

Se o formato do caminho não for válido, JSON_QUERY retornará um erro.

COM WRAPPER DE MATRIZ

A função ANSI SQL JSON_QUERY retorna um objeto JSON ou array em um caminho especificado. Com suporte a curinga de array em expressões de caminho SQL/JSON, JSON_QUERY pode retornar propriedades especificadas dos elementos em um array JSON onde cada elemento é um objeto JSON. Como buscas por wildcard podem devolver múltiplos valores, especifique a WITH ARRAY WRAPPER cláusula com uma expressão de caminho SQL/JSON que contenha um curinga, intervalo ou lista para retornar os valores como um array JSON. A WITH ARRAY WRAPPER cláusula é suportada apenas se a entrada for do tipo json .

Considere o seguinte documento JSON:

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"
        }
    ]
}';

O caminho $.credit_cards aponta para uma matriz JSON em que cada elemento é um objeto JSON válido. Agora, a JSON_QUERY função pode ser usada com suporte curinga de matriz para retornar todos ou valores específicos da type propriedade, como:

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

A tabela a seguir mostra vários exemplos de expressão de caminho SQL/JSON com curinga e o valor retornado usando JSON_QUERY WITH ARRAY WRAPPER.

Valor de retorno

Retorna um fragmento JSON do tipo nvarchar(max). A ordenação do valor retornado é a mesma que a ordenação da expressão de entrada.

Se o valor não for um objeto ou uma matriz:

• No modo incerto, JSON_QUERY retorna nulo.

• No modo estrito, JSON_QUERY retorna um erro.

Remarks

Modo incerto e modo estrito

Considere o seguinte texto JSON:

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

A tabela a seguir compara o comportamento de JSON_QUERY no modo incerto e no modo estrito. Para obter mais informações sobre a especificação do modo de caminho opcional (frouxo ou estrito), consulte Expressões de caminho JSON no Mecanismo de Banco de Dados SQL.

Usar JSON_QUERY com FOR JSON

JSON_QUERY retorna um fragmento JSON válido. Como resultado, FOR JSON não escapa de caracteres especiais no valor retornado JSON_QUERY.

Se você estiver retornando resultados com FOR JSON e estiver incluindo dados que já estão no formato JSON (em uma coluna ou como resultado de uma expressão), encapsule os dados JSON_QUERY JSON sem o parâmetro de caminho .

Examples

A. Retornar um fragmento JSON

O exemplo a seguir mostra como retornar um fragmento JSON de uma coluna CustomFields nos resultados da consulta.

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

B. Incluir fragmentos JSON na saída FOR JSON

O exemplo a seguir mostra como incluir fragmentos JSON na saída da 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. Usar WITH ARRAY WRAPPER com JSON_QUERY função

O exemplo a seguir mostra o uso da WITH ARRAY WRAPPERJSON_QUERY função para retornar vários elementos de uma matriz JSON:

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;

Veja aqui o conjunto de resultados.

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

Conteúdo relacionado

• Expressões de caminho JSON no Mecanismo de Banco de Dados SQL
• Dados JSON no SQL Server