カテゴリ：

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

OBJECT_CONSTRUCT

引数から構築された OBJECT を返します。

こちらもご参照ください。

OBJECT_CONSTRUCT_KEEP_NULL

構文

OBJECT_CONSTRUCT( [<key>, <value> [, <key>, <value> , ...]] )

OBJECT_CONSTRUCT(*)

引数

key

キーと値のペアのキー。各キーは VARCHAR 値です。

value

キーに関連付けられた値。値はどのデータ型でも使用できます。

*

アスタリスク（ワイルドカード）を付けて呼び出す場合、 OBJECT 値は属性名をキー、関連する値を値として、指定されたデータから構築されます。以下の例をご参照ください。

関数にワイルドカードを渡す場合、ワイルドカードをテーブルの名前またはエイリアスで修飾することができます。例えば、 mytable というテーブルからすべての列を渡すには、以下のように指定します。

(mytable.*)

ILIKE と EXCLUDE キーワードを使ってフィルタリングすることもできます。

  • ILIKE は、指定されたパターンに一致する列名でフィルターします。許されるパターンは1つのみです。例:

    (* ILIKE 'col1%')

  • EXCLUDE は、指定された列に一致しない列名をフィルターします。例:

    (* EXCLUDE col1)

(* EXCLUDE (col1, col2))

修飾子は、これらのキーワードを使用する場合に有効です。以下の例では、 ILIKE キーワードを使用して、テーブル mytable 内のパターン col1% に一致するすべての列をフィルターします。

(mytable.* ILIKE 'col1%')

ILIKE と EXCLUDE キーワードは、1つの関数呼び出し内で組み合わせることはできません。

オブジェクト定数 でワイルドカードを指定することもできます。

この関数では、 ILIKE と EXCLUDE キーワードは SELECT リストまたは GROUP BY 句でのみ有効です。

ILIKE と EXCLUDE キーワードの詳細については、 SELECT の「パラメータ」セクションをご参照ください。

戻り値

型 OBJECT の値を返します。

使用上の注意

  • キーまたは値がNULL（つまりSQL NULL）の場合、キーと値のペアは結果のオブジェクトから省略されます。キーとしてNULLでない文字列と、値としてJSON null（すなわち``PARSE_JSON('NULL')``）で構成されるキーと値のペアは省略されません。詳細については、 NULL 値 をご参照ください。

  • 構築されたオブジェクトは、キーと値のペアの元の順序を保持するとは限りません。

  • 多くのコンテキストでは、OBJECT_CONSTRUCT関数の代わりに OBJECT定数 （OBJECTリテラル とも呼ばれます）を使用できます。

例

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

単純なオブジェクトを構築する

この例は、単純なオブジェクトを作成する方法を示しています。

SELECT OBJECT_CONSTRUCT('a', 1, 'b', 'BBBB', 'c', NULL);

+--------------------------------------------------+
| OBJECT_CONSTRUCT('A', 1, 'B', 'BBBB', 'C', NULL) |
|--------------------------------------------------|
| {                                                |
|   "a": 1,                                        |
|   "b": "BBBB"                                    |
| }                                                |
+--------------------------------------------------+

ワイルドカード（*）文字を使用してオブジェクトを構築する

この例では、ワイルドカード文字（*）を使用して、FROM句から属性名と値を取得します。

CREATE OR REPLACE TABLE demo_table_1 (province VARCHAR, created_date DATE);
INSERT INTO demo_table_1 (province, created_date) VALUES
  ('Manitoba', '2024-01-18'::DATE),
  ('Alberta', '2024-01-19'::DATE);

SELECT province, created_date
  FROM demo_table_1
  ORDER BY province;

+----------+--------------+
| PROVINCE | CREATED_DATE |
|----------+--------------|
| Alberta  | 2024-01-19   |
| Manitoba | 2024-01-18   |
+----------+--------------+

SELECT OBJECT_CONSTRUCT(*) AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

+---------------------------------+
| OC                              |
|---------------------------------|
| {                               |
|   "CREATED_DATE": "2024-01-19", |
|   "PROVINCE": "Alberta"         |
| }                               |
| {                               |
|   "CREATED_DATE": "2024-01-18", |
|   "PROVINCE": "Manitoba"        |
| }                               |
+---------------------------------+

この例では、:code:`*`を使用し、出力をフィルタリングするためのILIKEキーワードを含めます。

SELECT OBJECT_CONSTRUCT(* ILIKE 'prov%') AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

+--------------------------+
| OC                       |
|--------------------------|
| {                        |
|   "PROVINCE": "Alberta"  |
| }                        |
| {                        |
|   "PROVINCE": "Manitoba" |
| }                        |
+--------------------------+

この例では、:code:`*`を使用し、出力をフィルタリングするためのEXCLUDEキーワードを含めます。

SELECT OBJECT_CONSTRUCT(* EXCLUDE province) AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

+--------------------------------+
| OC                             |
|--------------------------------|
| {                              |
|   "CREATED_DATE": "2024-01-18" |
| }                              |
| {                              |
|   "CREATED_DATE": "2024-01-19" |
| }                              |
+--------------------------------+

この例は前の例と同じですが、 OBJECT_CONSTRUCT 関数の代わりにオブジェクト定数を使用しています。

SELECT {* EXCLUDE province} AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

+--------------------------------+
| OC                             |
|--------------------------------|
| {                              |
|   "CREATED_DATE": "2024-01-18" |
| }                              |
| {                              |
|   "CREATED_DATE": "2024-01-19" |
| }                              |
+--------------------------------+

これは、 * を使用した別の例です。この場合、属性名は指定されていないため、Snowflakeは COLUMN1、 COLUMN2 などを使用します。

SELECT OBJECT_CONSTRUCT(*) FROM VALUES(1,'x'), (2,'y');

+---------------------+
| OBJECT_CONSTRUCT(*) |
|---------------------|
| {                   |
|   "COLUMN1": 1,     |
|   "COLUMN2": "x"    |
| }                   |
| {                   |
|   "COLUMN1": 2,     |
|   "COLUMN2": "y"    |
| }                   |
+---------------------+

SQL NULLとJSON nullを使用してオブジェクトを構築する

この例では、SQL NULLと文字列``'null'``を使用してオブジェクトを構築します。

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

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

詳細については、 NULL 値 をご参照ください。

式を使用してオブジェクトを構築する

OBJECT_CONSTRUCT は、 JSON オブジェクトの値を追加、変更、または除外する式とクエリをサポートします。

SELECT OBJECT_CONSTRUCT(
    'foo', 1234567,
    'dataset_size', (SELECT COUNT(*) FROM demo_table_1),
    'distinct_province', (SELECT COUNT(DISTINCT province) FROM demo_table_1),
    'created_date_seconds', extract(epoch_seconds, created_date)
  )  AS json_object
  FROM demo_table_1;

+---------------------------------------+
| JSON_OBJECT                           |
|---------------------------------------|
| {                                     |
|   "created_date_seconds": 1705536000, |
|   "dataset_size": 2,                  |
|   "distinct_province": 2,             |
|   "foo": 1234567                      |
| }                                     |
| {                                     |
|   "created_date_seconds": 1705622400, |
|   "dataset_size": 2,                  |
|   "distinct_province": 2,             |
|   "foo": 1234567                      |
| }                                     |
+---------------------------------------+

ネストされたOBJECT値を構築する

次の例では、テーブルを作成し、2階層のネスト構造を持つ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            |
|    |   }                            |
|    | }                              |
+----+--------------------------------+