Catégories :

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

OBJECT_INSERT

Renvoie un objet constitué de l’objet de l’entrée ainsi que d’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

L’objet source dans lequel la nouvelle paire clé-valeur est insérée.

key

La nouvelle clé à insérer dans l’objet. Doit être différente de toutes les clés existantes dans l’objet, à 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 est utilisée pour mettre à jour / remplacer une clé existante dans l’objet, plutôt que d’insérer une nouvelle paire clé-valeur.

La valeur par défaut est FALSE.

Notes sur l’utilisation

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

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

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

  • 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 l’objet génère une erreur.

  • Si l’indicateur de mise à jour est défini sur true, mais que la clé correspondante n’existe pas déjà dans l’objet, la paire clé/valeur est ajoutée.

  • Pour des OBJECTs structurés :

    • 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 l’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 un OBJECT structuré. Le type de l’OBJECT comprend la nouvelle clé insérée. Par exemple, supposons que vous ajoutiez la clé zipcode avec la valeur DOUBLE 94402 :

        SELECT
          OBJECT_INSERT(
            {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
            'zip_code',
            94402::DOUBLE,
            false
          ) AS new_object,
          SYSTEM$TYPEOF(new_object);
        
        Copy

        La fonction renvoie un OBJECT du type OBJECT(ville VARCHAR, état VARCHAR, code postal DOUBLE) :

        +-------------------------------------+---------------------------------------------------------------------------------------+
        | NEW_OBJECT                          | SYSTEM$TYPEOF(NEW_OBJECT)                                                             |
        |-------------------------------------+---------------------------------------------------------------------------------------|
        | {                                   | 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 DOUBLE, si bien que le type de zipcode est DOUBLE.

    • 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 l’OBJECT, il se produit une erreur.

      • La fonction renvoie un OBJECT structuré du même type.

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

Exemples

Insérez une troisième paire clé-valeur dans un objet contenant deux paires clé-valeur :

SELECT OBJECT_INSERT(OBJECT_CONSTRUCT('a',1,'b',2),'c',3);

----------------------------------------------------+
 OBJECT_INSERT(OBJECT_CONSTRUCT('A',1,'B',2),'C',3) |
----------------------------------------------------+
 {                                                  |
   "a": 1,                                          |
   "b": 2,                                          |
   "c": 3                                           |
 }                                                  |
----------------------------------------------------+
Copy

Insérez deux nouvelles paires clé-valeur, tout en omettant une paire clé-valeur, dans un objet vide :

  • Key_One comprend une valeur JSON NULL.

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

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

SELECT
  OBJECT_INSERT(OBJECT_INSERT(OBJECT_INSERT(OBJECT_CONSTRUCT(), 'Key_One', PARSE_JSON('NULL')), 'Key_Two', NULL), 'Key_Three', 'null')
  AS obj;

-----------------------+
          OBJ          |
-----------------------+
 {                     |
   "Key_One": null,    |
   "Key_Three": "null" |
 }                     |
-----------------------+
Copy

Mettre à jour une paire clé-valeur existante ("k1": 100) avec une nouvelle valeur ("string-value") :

SELECT OBJECT_INSERT(OBJECT_INSERT(OBJECT_CONSTRUCT(),'k1', 100),'k1','string-value', TRUE) AS obj;

------------------------+
          OBJ           |
------------------------+
 {                      |
   "k1": "string-value" |
 }                      |
------------------------+
Copy