Kategorien:: Funktionen für semistrukturierte und strukturierte Daten (Array/Objekt)

OBJECT_INSERT¶

Gibt einen OBJECT-Wert zurück, der aus dem Eingabe-OBJECT-Wert mit einem neuen Schlüssel-Wert-Paar besteht, das eingefügt wurde (oder einem bestehenden Schlüssel, der mit einem neuen Wert aktualisiert wurde).

Syntax¶

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

Copy

Argumente¶

Benötigt:

object: Der Quell-OBJECT-Wert, in den das neue Schlüssel-Wert-Paar eingefügt wird oder in dem ein bestehendes Schlüssel-Wert-Paar aktualisiert wird.
key: Der neue Schlüssel, der in den OBJECT-Wert eingefügt werden soll, oder ein vorhandener Schlüssel, dessen Wert aktualisiert wird. Der angegebene Schlüssel muss sich von allen vorhandenen Schlüsseln im OBJECT-Wert unterscheiden, es sei denn, updateFlag ist auf TRUE gesetzt.
value: Der dem Schlüssel zugeordnete Wert.

Optional:

updateFlag

Ein Boolesches Flag, das bei der Einstellung TRUE angibt, dass der Eingabewert zum Aktualisieren/Überschreiben eines vorhandenen Schlüssels im OBJECT-Wert verwendet werden soll, anstatt ein neues Schlüssel-Wert-Paar einzufügen.

Der Standardwert ist FALSE.

Rückgabewerte¶

Diese Funktion gibt einen Wert vom Datentyp OBJECT zurück.

Nutzungshinweise¶

Die Funktion unterstützt JSON Null-Werte, aber keine SQL NULL-Werte oder Schlüssel:
- Wenn key eine beliebige andere Zeichenfolge als NULL ist und value eine JSON-Null ist (zum Beispiel PARSE_JSON('null')), wird das Schlüssel-Wert-Paar in den zurückgegebenen OBJECT-Wert eingefügt.
- Wenn entweder key oder value eine SQL NULL ist, wird das Schlüssel-Wert-Paar im zurückgegebenen OBJECT-Wert weggelassen.
Wenn das optionale updateFlag-Argument auf TRUE gesetzt ist, wird der vorhandene Eingabeschlüssel key auf den Eingabewert value aktualisiert. Wenn updateFlag weggelassen oder auf FALSE gesetzt wird, führt das Aufrufen dieser Funktion mit einem Eingabeschlüssel, der bereits im OBJECT-Wert vorhanden ist, zu einem Fehler.
Wenn das Aktualisierungskennzeichen auf TRUE gesetzt wird, der entsprechende Schlüssel aber noch nicht im OBJECT-Wert vorhanden ist, wird das Schlüssel-Wert-Paar hinzugefügt.

Für strukturierte OBJECT-Werte:

Für die Argumente, die Schlüssel sind, müssen Sie Konstanten angeben.

Wenn das Argument updateFlag den Wert FALSE hat (wenn Sie ein neues Schlüssel-Wert-Paar einfügen):

Wenn Sie einen Schlüssel angeben, der bereits im OBJECT-Wert vorhanden ist, tritt ein Fehler auf.

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.

Die Funktion gibt einen strukturierten OBJECT-Wert zurück. Der Typ des OBJECT-Werts enthält den neu eingefügten Schlüssel. Angenommen, Sie fügen den Schlüssel zipcode mit dem VARCHAR-Wert 94402 hinzu:

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

Copy

+------------------------+---------------------------------------------------------------------+
| NEW_OBJECT             | TYPE                                                                |
|------------------------+---------------------------------------------------------------------|
| {                      | OBJECT(city VARCHAR, state VARCHAR, zip_code VARCHAR NOT NULL)[LOB] |
|   "city": "San Mateo", |                                                                     |
|   "state": "CA",       |                                                                     |
|   "zip_code": "94402"  |                                                                     |
| }                      |                                                                     |
+------------------------+---------------------------------------------------------------------+

Der Typ des eingefügten Wertes bestimmt den Typ, der der OBJECT-Typdefinition hinzugefügt wird. In diesem Fall ist der Wert für zipcode ein in VARCHAR umgewandelter Wert, sodass zipcode den Typ VARCHAR hat.

Wenn das Argument updateFlag den Wert TRUE hat (wenn Sie ein vorhandenes Schlüssel-Wert-Paar ersetzen):
- Wenn Sie einen Schlüssel angeben, der im OBJECT-Wert nicht vorhanden ist, tritt ein Fehler auf.
- Die Funktion gibt einen strukturierten OBJECT-Wert desselben Typs zurück.
- Der Typ des eingefügten Wertes wird implizit umgewandelt (Koersion) in den Typ des vorhandenen Schlüssels.

Beispiele¶

Die folgenden Beispiele rufen die Funktion OBJECT_INSERT auf.

Hinzufügen und Aktualisieren von Schlüssel-Wert-Paaren¶

In den Beispielen wird die folgende Tabelle verwendet:

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"  |
| }                |
+------------------+

Hinzufügen eines neuen Schlüssel-Wert-Paars zu einem OBJECT-Wert¶

Einfügen eines dritten Schlüssel-Wert-Paars in einen OBJECT-Wert, der zwei Schlüssel-Wert-Paare enthält:

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"  |
| }                |
+------------------+

Einfügen von zwei neuen Schlüssel-Wert-Paaren in den OBJECT-Wert, wobei ein Schlüssel-Wert-Paar weggelassen wird:

d besteht aus einem JSON-Nullwert.

e besteht aus einem SQL NULL-Wert und wird daher weggelassen.

f besteht aus einer Zeichenfolge, die „null“ enthält.

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"    |
| }                |
+------------------+

Aktualisieren eines Schlüssel-Wert-Paares in einem OBJECT-Wert¶

Aktualisieren eines vorhandenen Schlüssel-Wert-Paars ("b": "value2") im OBJECT-Wert mit einem neuen Wert ("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"    |
| }                |
+------------------+

Verschachtelte OBJECT-Werte hinzufügen und aktualisieren¶

Die Beispiele verwenden die folgende Tabelle mit verschachtelten OBJECT-Werten:

CREATE OR REPLACE TABLE sample_nested_object (
  id INTEGER,
  nested_object OBJECT);

INSERT INTO sample_nested_object (id, nested_object)
  SELECT 1,
         OBJECT_CONSTRUCT(
           'outer_key1', OBJECT_CONSTRUCT('inner_key1A', 'example1', 'inner_key1B', 'example2'),
           'outer_key2', OBJECT_CONSTRUCT('inner_key2', 5)
         );

INSERT INTO sample_nested_object (id, nested_object)
  SELECT 2,
         OBJECT_CONSTRUCT(
           'outer_key1', OBJECT_CONSTRUCT('inner_key1A', 'example3', 'inner_key1B', 'example4'),
           'outer_key2', OBJECT_CONSTRUCT('inner_key2', 7)
         );

SELECT * FROM sample_nested_object;

Copy

+----+--------------------------------+
| ID | NESTED_OBJECT                  |
+----+--------------------------------+
| 1  | {                              |
|    |   "outer_key1": {              |
|    |     "inner_key1A": "example1", |
|    |     "inner_key1B": "example2"  |
|    |   },                           |
|    |   "outer_key2": {              |
|    |     "inner_key2": 5            |
|    |   }                            |
|    | }                              |
| 2  | {                              |
|    |   "outer_key1": {              |
|    |     "inner_key1A": "example3", |
|    |     "inner_key1B": "example4"  |
|    |   },                           |
|    |   "outer_key2": {              |
|    |     "inner_key2": 7            |
|    |   }                            |
|    | }                              |
+----+--------------------------------+

Den verschachtelten OBJECT-Werten neue verschachtelte Schlüssel-Wert-Paare hinzufügen¶

Im folgenden Beispiel werden den verschachtelten OBJECT-Werten in der Tabelle neue verschachtelte Schlüssel-Wert-Paare hinzugefügt. Hierbei wird ein CASE-Ausdruck verwendet, um das hinzugefügte Schlüssel-Wert-Paar für jede Zeile anzugeben:

UPDATE sample_nested_object
  SET nested_object = OBJECT_INSERT(
    nested_object,
    'outer_key1',
     OBJECT_INSERT(
       nested_object:outer_key1,
       'inner_key1C',
       CASE
         WHEN id = 1 THEN 'added_value_1'
         WHEN id = 2 THEN 'added_value_2'
       END,
       TRUE
      ),
    TRUE);

SELECT * FROM sample_nested_object;

Copy

+----+------------------------------------+
| ID | NESTED_OBJECT                      |
|----+------------------------------------|
|  1 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example1",     |
|    |     "inner_key1B": "example2",     |
|    |     "inner_key1C": "added_value_1" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 5                |
|    |   }                                |
|    | }                                  |
|  2 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example3",     |
|    |     "inner_key1B": "example4",     |
|    |     "inner_key1C": "added_value_2" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 7                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+

Aktualisieren von Schlüssel-Wert-Paaren in verschachtelten OBJECT-Werten¶

Im folgenden Beispiel werden verschachtelte Schlüssel-Wert-Paare in den OBJECT-Werten in der Tabelle aktualisiert:

UPDATE sample_nested_object
  SET nested_object = OBJECT_INSERT(
    nested_object,
    'outer_key2',
    OBJECT_INSERT(
      nested_object:outer_key2,
      'inner_key2',
      CASE
        WHEN id = 1 THEN 6
        WHEN id = 2 THEN 8
      END,
      TRUE),
    TRUE);

SELECT * FROM sample_nested_object;

Copy

+----+------------------------------------+
| ID | NESTED_OBJECT                      |
|----+------------------------------------|
|  1 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example1",     |
|    |     "inner_key1B": "example2",     |
|    |     "inner_key1C": "added_value_1" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 6                |
|    |   }                                |
|    | }                                  |
|  2 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example3",     |
|    |     "inner_key1B": "example4",     |
|    |     "inner_key1C": "added_value_2" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 8                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+