AnalyticDB:Fonctions JSON

• JSON_ARRAY_CONTAINS : Vérifie si un tableau JSON contient la value spécifiée.

• JSON_ARRAY_LENGTH : Renvoie la longueur d'un tableau JSON.

• JSON_CONTAINS (versions 3.1.5.0 et ultérieures) : Vérifie si le chemin spécifié contient la valeur candidate. Si aucun chemin n'est spécifié, cette fonction vérifie si la cible contient la valeur candidate.

• JSON_CONTAINS_PATH (versions 3.1.5.0 et ultérieures) : Vérifie si un document JSON contient tout ou partie des chemins spécifiés.

• JSON_EXTRACT : Extrait des données d'un document JSON au json_path spécifié.

• JSON_KEYS : Si json_path est spécifié, cette fonction renvoie toutes les clés du chemin indiqué dans un document JSON. Sinon, elle renvoie toutes les clés du chemin racine (json_path='$').

• JSON_OVERLAPS (versions 3.1.10.6 et ultérieures) : Vérifie si un document JSON contient l'un des éléments spécifiés, tels que candidate1, candidate2 ou candidate3.

• JSON_REMOVE (versions 3.1.10.0 et ultérieures) : Supprime l'élément situé au json_path spécifié d'un document json et renvoie la chaîne modifiée. Vous pouvez spécifier plusieurs éléments à supprimer à l'aide de array[json_path,json_path,...].

• JSON_SIZE : Renvoie la taille de l'objet ou du tableau JSON situé au json_path spécifié.

• JSON_SET (versions 3.2.2.8 et ultérieures) : Insère ou met à jour des données dans un document json au json_path spécifié, puis renvoie le document json mis à jour.

• JSON_UNQUOTE (versions 3.2.2.11 et ultérieures) : Supprime les guillemets doubles de json_value, interprète les caractères d'échappement spécifiques dans json_value, et renvoie la valeur résultante.

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• Description : Vérifie si un tableau JSON contient la value spécifiée.

• Type de valeur d'entrée : value peut être de type numérique, chaîne ou BOOLEAN.

• Type de valeur de retour : BOOLEAN.

• Exemple :

  • Vérifiez si le tableau JSON [1, 2, 3] contient l'élément 2. Exécutez l'instruction suivante :

    SELECT json_array_contains('[1, 2, 3]', 2);

    Résultat :

    +-------------------------------------+
    | json_array_contains('[1, 2, 3]', 2) |
    +-------------------------------------+
    |                1                    |
    +-------------------------------------+

JSON_ARRAY_LENGTH

json_array_length(json)

• Description : Renvoie la longueur d'un tableau JSON.

• Type de valeur d'entrée : Chaîne ou JSON.

• Type de valeur de retour : BIGINT.

• Exemple :

  • Obtenez la longueur du tableau JSON [1, 2, 3]. Exécutez l'instruction suivante :

    SELECT json_array_length('[1, 2, 3]');

    Résultat :

    +--------------------------------+
    | json_array_length('[1, 2, 3]') |
    +--------------------------------+
    |                 3              |
    +--------------------------------+

JSON_CONTAINS

La fonction JSON_CONTAINS vérifie si un document JSON spécifié contient une valeur donnée. L'utilisation d'un index de tableau JSON dans vos requêtes évite les analyses complètes de table ou l'analyse syntaxique de l'intégralité du document JSON, améliorant ainsi l'efficacité des requêtes.

json_contains(target, candidate[, json_path])

json_contains(json_path, cast('[candidate1,candidate2,candidate3]' as json)) 

JSON_CONTAINS_PATH

json_contains_path(json, one_or_all, json_path[, json_path,...])

• Description : Vérifie si le chemin spécifié existe dans l'objet JSON.

  • Si one_or_all vaut 'one', la fonction renvoie 1 si le document JSON contient au moins l'un des chemins spécifiés. Sinon, elle renvoie 0.

  • Si one_or_all vaut 'all', la fonction renvoie 1 si le document JSON contient tous les chemins spécifiés. Sinon, elle renvoie 0.

• Type de valeur d'entrée : json est de type JSON. one_or_all est de type VARCHAR et accepte les valeurs 'one' ou 'all' (insensible à la casse). json_path est une expression de chemin.

• Type de valeur de retour : BOOLEAN.

• Exemples :

  • Vérifiez si le document JSON contient au moins l'un des chemins $.a et $.e. Exécutez l'instruction suivante :

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') AS RESULT;

    Résultat :

    +--------+
    | result |
    +--------+
    |      1 |
    +--------+

  • Vérifiez si le document JSON contient à la fois les chemins $.a et $.e. Exécutez l'instruction suivante :

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') AS RESULT;

    Résultat :

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_EXTRACT

json_extract(json, json_path)

• Description : Extrait une valeur d'un document JSON au json_path spécifié. Si une clé du document json contient des caractères spéciaux, tels que $ ou ., utilisez le format json_path suivant : '$["Key"]'.

  Par exemple, si la clé est $data, json_path doit être '$["$data"]'.

• Type de valeur d'entrée : Chaîne ou JSON.

• Type de valeur de retour : JSON.

• Exemples :

  • Extrayez la valeur au chemin $[0] du tableau [10, 20, [30, 40]]. Exécutez l'instruction suivante :

    SELECT json_extract('[10, 20, [30, 40]]', '$[0]');

    Résultat :

    +-------------------------------------------+
    | json_extract('[10, 20, [30, 40]]', '$[0]') |
    +-------------------------------------------+
    |                     10                    |
    +-------------------------------------------+

  • Extrayez la valeur du chemin $date depuis {"id":"1","$date":"12345"}. Exécutez l'instruction suivante :

    SELECT JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]');

    Résultat :

    +---------------------------------------------------------+
    |JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]') |
    +---------------------------------------------------------+
    |                       "12345"                           |
    +---------------------------------------------------------+

JSON_KEYS

json_keys(json[, json_path])

• Description

  • Si json_path est spécifié, cette fonction renvoie toutes les clés du chemin indiqué dans le document JSON.

  • Si json_path n'est pas spécifié, cette fonction renvoie toutes les clés du chemin racine (json_path='$').

• Type de valeur d'entrée : Seuls les paramètres de type JSON sont pris en charge.

  Vous pouvez construire des données JSON des manières suivantes :

  • Utilisez directement des données JSON. Par exemple, json '{"a": 1, "b": {"c": 30}}'.

  • Convertissez explicitement une chaîne en données JSON à l'aide de la fonction CAST. Par exemple, CAST('{"a": 1, "b": {"c": 30}}' AS json).

• Type de valeur de retour : JSON ARRAY.

• Exemples :

  • Obtenez toutes les clés du chemin $.b. Exécutez l'instruction suivante :

    SELECT json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b');

    Résultat :

    +-----------------------------------------------------------+
    | json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b') |
    +-----------------------------------------------------------+
    |                           ["c"]                           |
    +-----------------------------------------------------------+

  • Obtenez toutes les clés du chemin racine. Exécutez l'instruction suivante :

    SELECT JSON_KEYS(json '{"a": 1, "b": {"c": 30}}');

    Résultat :

    +--------------------------------------------+
    | JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') |
    +--------------------------------------------+
    |             ["a","b"]                      |
    +--------------------------------------------+

JSON_OVERLAPS

json_overlaps(json, cast('[candidate1,candidate2,candidate]' as json)) 

• Description : Vérifie si le document JSON spécifié contient l'un des éléments indiqués, tels que candidate1, candidate2 ou candidate3.

• Types de données des valeurs d'entrée : candidate1,candidate2,candidate3,... peuvent être de type numérique ou chaîne, mais toutes les valeurs doivent partager le même type de données.

• Type de valeur de retour : VARCHAR.

• Exemples :

  • Récupérez les données de la colonne JSON vj contenant CP-018673.

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('["CP-018673"]' AS json));

    Résultat :

    +-----+----------------------------------------------------------------------------+
    |  id |   vj                                                                       |
    +-----+----------------------------------------------------------------------------+
    |  2  | ["CP-018673", 1, false]                                                    |
    +-----+----------------------------------------------------------------------------+
    |  3  | ["CP-018673", 1, false, {"a": 1}]                                          |
    +-----+----------------------------------------------------------------------------+
    |  5  | ["CP-018673","CP-018671","CP-018672","CP-018670","CP-018669","CP-018668"]  |
    +-----+----------------------------------------------------------------------------+

  • Récupérez les données de la colonne JSON vj contenant l'un des éléments 1, 2 ou 3.

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('[1,2,3]' AS json))

    Résultat :

    +-----+-------------------------------------+
    |  id |                 vj                  |
    +-----+-------------------------------------+
    |  1  | [1,2,3]                             |
    +-----+-------------------------------------+
    |  2  | ["CP-018673", 1, false]             |
    +-----+-------------------------------------+
    |  3  | ["CP-018673", 1, false, {"a": 1}]   |
    +-----+-------------------------------------+

JSON_REMOVE

json_remove(json,json_path)
json_remove(json,array[json_path,json_path,...])

• Description : Supprime l'élément situé au json_path spécifié d'un document json et renvoie la chaîne modifiée. Vous pouvez spécifier plusieurs éléments à supprimer à l'aide de array[json_path,json_path,...].

• Type de valeur d'entrée : json est une chaîne VARCHAR au format JSON. json_path est une chaîne VARCHAR au format JSON.

• Type de valeur de retour : VARCHAR.

• Exemples

  • Supprimez l'élément au chemin $.glossary.GlossDiv et obtenez la chaîne modifiée. Exécutez l'instruction suivante :

      SELECT json_remove(
      '{
          "glossary": {
              "title": "example glossary",
              "GlossDiv": {
                  "title": "S",
                  "GlossList": {
                      "GlossEntry": {
                          "ID": "SGML",
                          "SortAs": "SGML",
                          "GlossTerm": "Standard Generalized Markup Language",
                          "Acronym": "SGML",
                          "Abbrev": "ISO 8879:1986",
                          "GlossDef": {
                              "para": "A meta-markup language, used to create markup languages such as DocBook.",
                              "GlossSeeAlso": ["GML", "XML"]
                          },
                          "GlossSee": "markup"
                      }
                  }
              }
          }
      }'
      , '$.glossary.GlossDiv') a;

    Résultat :

    {"glossary":{"title":"example glossary"}}

  • Supprimez les éléments aux chemins $.glossary.title et $.glossary.GlossDiv.title, puis obtenez la chaîne modifiée. Exécutez l'instruction suivante :

    SELECT json_remove(
      '{
          "glossary": {
              "title": "example glossary",
              "GlossDiv": {
                  "title": "S",
                  "GlossList": {
                      "GlossEntry": {
                          "ID": "SGML",
                          "SortAs": "SGML",
                          "GlossTerm": "Standard Generalized Markup Language",
                          "Acronym": "SGML",
                          "Abbrev": "ISO 8879:1986",
                          "GlossDef": {
                              "para": "A meta-markup language, used to create markup languages such as DocBook.",
                              "GlossSeeAlso": ["GML", "XML"]
                          },
                          "GlossSee": "markup"
                      }
                  }
              }
          }
      }'
      , array['$.glossary.title', '$.glossary.GlossDiv.title']) a;

    Résultat :

    {"glossary":{"GlossDiv":{"GlossList":{"GlossEntry":{"GlossTerm":"Standard Generalized Markup Language","GlossSee":"markup","SortAs":"SGML","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"ID":"SGML","Acronym":"SGML","Abbrev":"ISO 8879:1986"}}}}}

JSON_SIZE

json_size(json, json_path)

• Description : Renvoie la taille d'un objet ou d'un tableau JSON au json_path spécifié.

  Remarque

  Si json_path ne pointe pas vers un objet ou un tableau JSON, cette fonction renvoie 0.

• Type de valeur d'entrée : Chaîne ou JSON.

• Type de valeur de retour : BIGINT.

• Exemples :

  • Le json_path pointe vers un objet JSON. Exécutez l'instruction suivante :

    SELECT json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;

    Résultat :

    +--------+
    | result |
    +--------+
    |      2 |
    +--------+

  • Le json_path ne pointe pas vers un objet ou un tableau JSON. Exécutez l'instruction suivante :

    SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;

    Résultat :

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_SET

json_set(json, json_path, value[, json_path, value] ...)

• Description : Insère ou met à jour des données dans un document json au json_path spécifié, puis renvoie le document json mis à jour.

  • Si json ou json_path est null, la fonction renvoie null.

  • Si le document json n'est pas dans un format JSON valide, ou si un json_path n'est pas une expression de chemin valide, une exception est levée.

  • Si le json_path spécifié existe, sa valeur est remplacée par value.

  • Si le json_path spécifié n'existe pas dans le document json :

    • Si json_path pointe vers un objet JSON, value est ajoutée en tant que nouvel élément à l'emplacement spécifié par json_path.

    • Si json_path pointe vers un tableau JSON, la fonction vérifie si des données existent à la position précédant le json_path spécifié. En l'absence de données, des valeurs null comblent l'écart avant l'insertion de value. Sinon, value est insérée directement.

    • Dans les autres cas, une exception est levée.

• Types de valeurs d'entrée :

  • json : VARCHAR ou JSON.

  • json_path : VARCHAR.

  • value : BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, VARCHAR, VARBINARY, DATE, DATETIME, TIMESTAMP ou TIME.

• Type de valeur de retour : JSON.

• Exemples :

  • Insérez des données dans un document json où json_path est null.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', null, '10');

    Résultat :

    +------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}', NULL, '10') |
    +------------------------------------------------+
    | null                                           |
    +------------------------------------------------+

  • Insérez des données dans un document json où json_path n'est pas une expression de chemin valide.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.c', '10');

    Résultat :

    Failed to execute json_set() for json_path: $.b.c

  • Insérez des données dans un document json où json_path1 existe, tandis que json_path2 n'existe pas et pointe vers un objet JSON.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');

    Résultat :

    +-----------------------------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------------------------+
    | {"a":10,"b":[2,3],"c":"[true, false]"}                                |
    +-----------------------------------------------------------------------+

  • Insérez des données dans un document json où le json_path spécifié n'existe pas et pointe vers un tableau JSON.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]');

    Résultat :

    +----------------------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]') |
    +----------------------------------------------------------------+
    | {"a":1,"b":[2,3,null,null,"[true, false]"]}                    |
    +----------------------------------------------------------------+

JSON_UNQUOTE

json_unquote(json_value)

• Cette commande supprime les guillemets doubles de json_value, interprète certains caractères d'échappement et renvoie la valeur résultante.

  AnalyticDB for MySQL ne valide pas json_value. Cette fonction traite la valeur selon la logique décrite, que json_value respecte ou non la syntaxe JSON.

  Les caractères d'échappement pris en charge figurent dans le tableau suivant.

  Avant interprétation	Après interprétation
  \"	Guillemet double (").
  \b	Retour arrière.
  \f	Saut de page.
  \n	Saut de ligne.
  \r	Retour chariot.
  \t	Tabulation.
  \\	Barre oblique inverse (\).
  \uXXXX	Caractère UTF-8.

• Type de valeur d'entrée : VARCHAR.

• Type de valeur de retour : VARCHAR.

• Exemples :

  • Obtenez la chaîne sans guillemets abc. Exécutez l'instruction suivante :

    SELECT json_unquote('"abc"');

    Résultat :

    +-----------------------+
    | json_unquote('"abc"') |
    +-----------------------+
    |          abc          |
    +-----------------------+

  • L'instruction suivante renvoie la chaîne interprétée et sans guillemets :

    SELECT json_unquote('"\\t\\u0032"');

    Résultat :

    +------------------------------+
    | json_unquote('"\\t\\u0032"') |
    +------------------------------+
    |               2              |
    +------------------------------+

Annexe : Syntaxe JSON Path

Utilisation

• Utilisez $.keyName[.keyName]... pour accéder à une clé spécifique d'un objet JSON.

• Utilisez $[nonNegativeInteger] pour accéder au Nième élément d'un tableau JSON, où n est un entier non négatif.

• Utilisez $.keyName[.keyName]...[nonNegativeInteger] pour accéder au Nième élément d'un tableau JSON imbriqué dans un objet JSON, où n est un entier non négatif.

Remarques

La syntaxe JSON Path d'AnalyticDB for MySQL ne prend pas en charge les caractères génériques * et **. Par conséquent, les expressions telles que '$.*', '$.hobbies[*]', '$.address.**' et '$.hobbies.**' ne sont pas prises en charge.

Exemples

Prenons les données JSON suivantes.

{
    "name": "Alice",
    "age": 25,
    "address": {
        "city": "Hangzhou",
        "zip": "10001"
    },
    "hobbies":["reading", "swimming", "cycling"]
}

FAQ

Références

• JSON : Décrit le type de données JSON.

• Index JSON : Explique comment créer des index pour les objets et tableaux JSON.