カテゴリ：: 半構造化データ関数と構造化データ関数（配列/オブジェクト）

OBJECT_INSERT¶

新しいキーと値のペア（または新しい値で更新された既存のキー）が挿入された入力 OBJECT 値で構成される OBJECT 値を返します。

構文¶

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

引数¶

必須：

object: 新しいキー値のペアが挿入されるか、既存のキー値のペアが更新されるソース OBJECT 値。
key: OBJECT 値に挿入する新しいキー、または値を更新する既存のキー。指定されたキーは、 updateFlag が TRUE に設定されている場合を除き、 OBJECT 値にある既存のすべてのキーと異なっている必要があります。
value: キーに関連付けられた値です。

オプション：

updateFlag

ブールフラグ。TRUE に設定すると、入力値は、新しいキーと値のペアを挿入するのではなく、 OBJECT 値にある既存のキーの値を更新することを指定します。

デフォルトは FALSE です。

戻り値¶

この関数は、 OBJECT データ型を持つ値を返します。

使用上の注意¶

関数は JSON null 値をサポートしますが、 SQL NULL 値またはキーはサポートしません。
- key が NULL 以外の文字列で、 value が JSON null（例: PARSE_JSON('null')）の場合は、キーと値のペアが返された OBJECT 値に挿入されます。
- key または value のいずれかが SQL NULL の場合は、返される OBJECT 値からキーと値のペアが省略されます。
オプションの updateFlag 引数が TRUE に設定されている場合、既存の入力 key は入力 value に更新されます。updateFlag が省略されているか、 FALSE に設定されている場合は、 OBJECT 値にすでに存在する入力キーでこの関数を呼び出すとエラーになります。
更新フラグが TRUE に設定されているが、対応するキーが OBJECT 値にまだ存在しない場合は、キーと値のペアが追加されます。

構造化 OBJECT 値の場合:

キーとなる引数には、定数を指定する必要があります。

updateFlag 引数が FALSE の場合（新しいキーと値のペアを挿入する場合）:

OBJECT 値にすでに存在するキーを指定するとエラーになります。

SELECT OBJECT_INSERT(
  {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
  'city',
  'San Jose',
  false
);

093202 (23001): Function OBJECT_INSERT:
  expected structured object to not contain field city but it did.

この関数は、構造化 OBJECT 値を返します。OBJECT 値の型には、新しく挿入されたキーが含まれます。例えば、 zipcode 値の VARCHAR を持つ 94402 キーを追加するとします。

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;

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

挿入される値の型は OBJECT 型の定義に追加される型を決定します。この場合、 zipcode の値は VARCHAR にキャストされた値であるため、 zipcode の型は VARCHAR になります。

updateFlag 引数が TRUE の場合（既存のキーと値のペアを置き換える場合）:
- OBJECT 値に存在しないキーを指定するとエラーになります。
- この関数は、同じ型の構造化 OBJECT 値を返します。
- 挿入された値の型は、既存のキーの型に強制されます。

例¶

以下の例では、 OBJECT_INSERT関数を呼びだしています。

キーと値のペアの追加および更新¶

例では、次のテーブルを使用します。

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;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2"  |
| }                |
+------------------+

OBJECT 値に新しいキーと値のペアを追加する¶

2つのキーと値のペアを持つ OBJECT 値に、3つ目のキーと値のペアを挿入します。

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

SELECT * FROM object_insert_examples;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3"  |
| }                |
+------------------+

OBJECT 値に新しいキーと値のペアを2つ挿入し、キーと値のペアを1つ省略します。

d は、 JSON null値で構成されています。

e は、 SQL NULL 値で構成されるため、省略されます。

f は、「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;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+

OBJECT値のキーと値のペアの更新¶

OBJECT 値の既存のキーと値のペア（"b": "value2"）を新しい値（"valuex"）で更新します。

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

SELECT * FROM object_insert_examples;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "valuex", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+

ネストされたOBJECT値の追加および更新¶

例では、ネストされたOBJECT値を持つ以下のテーブルを使用します。

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;

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

ネストされたOBJECT値に新しいネストされたキーと値のペアを追加¶

次の例では、テーブルのネストされたOBJECT値に、新しいネストされたキーと値のペアを追加します。:doc:`/sql-reference/functions/case`式を使用して、行ごとに追加するキーと値のペアを指定しています。

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;

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

ネストされたOBJECT値のキーと値のペアの更新¶

次の例では、テーブルのOBJECT値内のネストされたキーと値のペアを更新します。

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;

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