カテゴリ：

半構造化データ関数と構造化データ関数 （配列/オブジェクト）

OBJECT_INSERT

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

構文

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

引数

必須：

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
);
        Copy
        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;
        Copy
        +------------------------+---------------------------------------------------------------------+
| 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;
Copy
+------------------+
| 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;
Copy
+------------------+
| 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;
Copy
+------------------+
| 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;
Copy
+------------------+
| 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;
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            |
|    |   }                            |
|    | }                              |
+----+--------------------------------+

ネストされた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;
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                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+

ネストされた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;
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                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+