Catégories :

Fonctions de données semi-structurées et structurées (tableau/objet)

OBJECT_INSERT

Renvoie une valeur OBJECT composée de la valeur OBJECT de l’entrée ainsi qu’une nouvelle paire clé-valeur insérée (ou une clé existante mise à jour avec une nouvelle valeur).

Syntaxe

OBJECT_INSERT( <object> , <key> , <value> [ , <updateFlag> ] )
Copy

Arguments

Obligatoire :

object

La valeur OBJECT source dans laquelle la nouvelle paire clé-valeur est insérée ou dans laquelle une paire clé-valeur existante est mise à jour.

key

La nouvelle clé à insérer dans la valeur OBJECT ou une clé existante dont la valeur est mise à jour. La clé spécifiée doit être différente de toutes les clés existantes dans la valeur OBJECT, à moins que updateFlag ne soit défini sur TRUE.

value

La valeur associée à la clé.

Facultatif :

updateFlag

Indicateur booléen qui, lorsqu’il est défini sur TRUE, spécifie que la valeur d’entrée met à jour la valeur d’une clé existante dans la valeur OBJECT, plutôt que d’insérer une nouvelle paire clé-valeur.

La valeur par défaut est FALSE.

Renvoie

Cette fonction renvoie une valeur ayant le type de données OBJECT.

Notes sur l’utilisation

  • La fonction prend en charge les valeurs nulles JSON, mais pas les valeurs ni les clés SQL NULL :

    • Si key est une chaîne autre que NULL et que value est une valeur nulle JSON (par exemple, PARSE_JSON('null')), la paire clé-valeur est insérée dans la valeur OBJECT renvoyée.

    • Si key ou value est un SQL NULL, la paire clé-valeur est omise de la valeur OBJECT renvoyée.

  • Si l’argument facultatif updateFlag est défini sur TRUE, l’argument key entré existant est mis à jour avec l’argument value entré. Si updateFlag est omis ou défini sur FALSE, l’appel de cette fonction avec une clé d’entrée déjà présente dans la valeur OBJECT génère une erreur.

  • Si l’indicateur de mise à jour est défini sur TRUE, mais que la clé correspondante n’existe pas encore dans la valeur OBJECT, la paire clé-valeur est ajoutée.

  • Pour les valeurs OBJECT structurées :

    • Pour les arguments qui sont des clés, vous devez spécifier des constantes.

    • Lorsque l’argument updateFlag est défini sur FALSE (lorsque vous insérez une nouvelle paire clé-valeur) :

      • Si vous spécifiez une clé qui existe déjà dans la valeur OBJECT, il se produit une erreur.

        SELECT OBJECT_INSERT(
          {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
          'city',
          'San Jose',
          false
        );
        
        Copy
        093202 (23001): Function OBJECT_INSERT:
          expected structured object to not contain field city but it did.
        
      • La fonction renvoie une valeur OBJECT structurée. Le type de la valeur OBJECT comprend la clé nouvellement insérée. Par exemple, supposons que vous ajoutiez la clé zipcode avec la valeur FLOAT 94402 :

        SELECT
          OBJECT_INSERT(
            {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
            'zip_code',
            94402::FLOAT,
            false
          ) AS new_object,
          SYSTEM$TYPEOF(new_object) AS type;
        
        Copy
        +-------------------------------------+---------------------------------------------------------------------------------------+
        | NEW_OBJECT                          | TYPE                                                                                  |
        |-------------------------------------+---------------------------------------------------------------------------------------|
        | {                                   | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216), zip_code FLOAT NOT NULL)[LOB] |
        |   "city": "San Mateo",              |                                                                                       |
        |   "state": "CA",                    |                                                                                       |
        |   "zip_code": 9.440200000000000e+04 |                                                                                       |
        | }                                   |                                                                                       |
        +-------------------------------------+---------------------------------------------------------------------------------------+
        

        Le type de la valeur insérée détermine le type ajouté à la définition de type OBJECT. Dans ce cas, la valeur de zipcode est une valeur transformée en FLOAT, si bien que le type de zipcode est FLOAT.

    • Lorsque l’argument updateFlag est défini sur TRUE (lorsque vous remplacez une paire clé-valeur existante) :

      • Si vous spécifiez une clé qui n’existe pas dans la valeur OBJECT, il se produit une erreur.

      • La fonction renvoie une valeur OBJECT structurée du même type.

      • Le type de la valeur insérée est contraint à se transformer dans le type de la clé existante.

Exemples

Les exemples utilisent la table suivante :

CREATE OR REPLACE TABLE object_insert_examples (object_column OBJECT);

INSERT INTO object_insert_examples (object_column)
  SELECT OBJECT_CONSTRUCT('a', 'value1', 'b', 'value2');

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2"  |
| }                |
+------------------+

Ajouter une nouvelle paire clé-valeur à une valeur OBJECT

Insérer une troisième paire clé-valeur dans une valeur OBJECT qui comporte deux paires clé-valeur :

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'c', 'value3');

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3"  |
| }                |
+------------------+

Insérer deux nouvelles paires clé-valeur dans la valeur OBJECT, tout en omettant une paire clé-valeur :

  • d comprend une valeur JSON nulle.

  • e comprend une valeur SQL NULL et est donc omis.

  • f consiste en une chaîne de caractères contenant « null ».

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'd', PARSE_JSON('null'));

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'e', NULL);

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'f', 'null');

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+

Mise à jour d’une paire clé-valeur dans une valeur OBJECT

Mettre à jour une paire clé-valeur existante ("b": "value2") dans la valeur OBJECT avec une nouvelle valeur ("valuex") :

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'b', 'valuex', TRUE);

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "valuex", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+