JSON_QUERY (Transact-SQL)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures d’Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics SQL Analyticsdans Microsoft FabricWarehouse dans microsoft FabricSQL Database dans Microsoft Fabric SQL Database dans Microsoft Fabric

La JSON_QUERY syntaxe extrait un objet ou un tableau à partir d’une chaîne JSON.

Pour extraire une valeur scalaire d’une chaîne JSON au lieu d’un objet ou d’un tableau, consultez JSON_VALUE. Pour plus d’informations sur les différences entre JSON_VALUE et JSON_QUERY, consultez Comparer JSON_VALUE et JSON_QUERY.

Conventions de la syntaxe Transact-SQL

Syntax

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

Arguments

expression

Expression. En règle générale, nom d’une variable ou d’une colonne qui contient du texte JSON.

Si JSON_QUERY le code JSON n’est pas valide dans l’expression avant de trouver la valeur identifiée par le chemin d’accès, la fonction retourne une erreur. S’il JSON_QUERY ne trouve pas la valeur identifiée par le chemin d’accès, il analyse l’intégralité du texte et retourne une erreur si elle trouve JSON qui n’est pas valide n’importe où dans l’expression.

path

Chemin JSON qui spécifie l’objet ou le tableau à extraire.

Dans SQL Server 2017 (14.x) et dans Azure SQL Database, vous pouvez fournir une variable comme valeur de chemin d’accès.

Le chemin JSON peut spécifier le mode lax ou strict pour l’analyse. Si vous ne spécifiez pas le mode d’analyse, le mode lax est utilisé par défaut. Pour plus d’informations, consultez les expressions de chemin JSON dans le moteur de base de données SQL.

La valeur par défaut du chemin d’accès est $. Par conséquent, si vous ne fournissez pas de valeur pour le chemin d’accès, JSON_QUERY retourne l’expression d’entrée.

Si le format du chemin d’accès n’est pas valide, JSON_QUERY retourne une erreur.

AVEC ENVELOPPE DE TABLEAU

La fonction SQL JSON_QUERY ANSI retourne un objet JSON ou un tableau sur un chemin spécifié. Avec le support des jokers de tableau dans les expressions de chemin SQL/JSON, il JSON_QUERY est possible de retourner des propriétés spécifiées des éléments dans un tableau JSON où chaque élément est un objet JSON. Comme les recherches par wildcard peuvent retourner plusieurs valeurs, spécifiez la WITH ARRAY WRAPPER clause avec une expression de chemin SQL/JSON contenant un joker, une plage ou une liste pour retourner les valeurs sous forme de tableau JSON. La WITH ARRAY WRAPPER clause n’est prise en charge que si l’entrée est de type json .

Considérez le document JSON suivant :

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

Le chemin $.credit_cards pointe vers un tableau JSON où chaque élément est un objet JSON valide. À présent, la fonction peut être utilisée avec la JSON_QUERY prise en charge des caractères génériques de tableau pour retourner toutes ou certaines valeurs de la type propriété comme :

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

Le tableau suivant présente différents exemples d’expression de chemin SQL/JSON avec un caractère générique et la valeur de retour à l’aide JSON_QUERY WITH ARRAY WRAPPERde .

Valeur retournée

Retourne un fragment JSON de type nvarchar(max). Le classement de la valeur renvoyée est le même que le classement de l’expression d’entrée.

Si la valeur n’est pas un objet ou un tableau :

• En mode lax, JSON_QUERY retourne null.

• En mode strict, JSON_QUERY retourne une erreur.

Remarks

Mode lax et mode strict

Considérons le texte JSON suivant :

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

Le tableau suivant compare le comportement de JSON_QUERY en mode lax et en mode strict. Pour plus d’informations sur la spécification facultative du mode de chemin d’accès (lax ou strict), consultez les expressions de chemin JSON dans le moteur de base de données SQL.

Utiliser JSON_QUERY avec FOR JSON

JSON_QUERY renvoie un fragment JSON valide. Par conséquent, FOR JSON n’échappe pas de caractères spéciaux dans la valeur de retour JSON_QUERY.

Si vous retournez des résultats avec FOR JSON et que vous incluez des données déjà au format JSON (dans une colonne ou à la suite d’une expression), encapsulez les données JSON_QUERY JSON sans le paramètre de chemin d’accès .

Examples

A. Retourner un fragment JSON

L’exemple suivant montre comment renvoyer un fragment JSON à partir d’une colonne CustomFields dans les résultats de la requête.

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

B. Inclure des fragments JSON dans la sortie FOR JSON

L’exemple suivant montre comment inclure les fragments JSON dans la sortie de la clause FOR JSON.

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

C. Utiliser WITH ARRAY WRAPPER avec JSON_QUERY fonction

L’exemple suivant montre l’utilisation de WITH ARRAY WRAPPER la JSON_QUERY fonction pour renvoyer plusieurs éléments à partir d’un tableau 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;

Voici l'ensemble des résultats.

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

Contenu connexe

• Expressions de chemin JSON dans le moteur de base de données SQL
• Données JSON dans SQL Server