Categories:

Semi-structured and structured data functions (Array/Object)

OBJECT_CONSTRUCT¶

Returns an OBJECT constructed from the arguments.

See also:

OBJECT_CONSTRUCT_KEEP_NULL

Syntax¶

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

OBJECT_CONSTRUCT( * )
Copy

Arguments¶

key

The key in a key-value pair. Each key is a VARCHAR value.

value

The value that is associated with the key. The value can be any data type.

*

When invoked with an asterisk (wildcard), the OBJECT value is constructed from the specified data using the attribute names as keys and the associated values as values. See the examples below.

You can also specify the wildcard in an object constant.

When it is specified in an object constant, the wildcard can be unqualified or qualified. For example, both of these wildcard specifications are valid:

SELECT {*} FROM my_table;

SELECT {t.*} FROM my_table;
Copy

Wildcards cannot be mixed with key-value pairs. For example, the following wildcard specification is not allowed:

SELECT {*, 'k': 'v'} FROM my_table;
Copy

More than one wildcard cannot be used in one object constant. For example, the following wildcard specification is not allowed:

SELECT {t1.*, t2.*} FROM t1, t2;
Copy

Returns¶

Returns a value of type OBJECT.

Usage notes¶

  • If the key or value is NULL (i.e. SQL NULL), the key-value pair is omitted from the resulting object. A key-value pair consisting of a string that is not NULL as the key and a JSON null as the value (i.e. PARSE_JSON('NULL')) is not omitted. For more information, see VARIANT null.

  • The constructed object does not necessarily preserve the original order of the key-value pairs.

  • In many contexts, you can use an OBJECT constant (also called an OBJECT literal) instead of the OBJECT_CONSTRUCT function.

Examples¶

This example shows how to construct a simple object:

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

This example uses * to get the attribute name and the value from the FROM clause:

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);
Copy
SELECT province, created_date
  FROM demo_table_1
  ORDER BY province;
Copy
+----------+--------------+
| PROVINCE | CREATED_DATE |
|----------+--------------|
| Alberta  | 2024-01-19   |
| Manitoba | 2024-01-18   |
+----------+--------------+

SELECT OBJECT_CONSTRUCT(*) AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];
Copy
+---------------------------------+
| OC                              |
|---------------------------------|
| {                               |
|   "CREATED_DATE": "2024-01-19", |
|   "PROVINCE": "Alberta"         |
| }                               |
| {                               |
|   "CREATED_DATE": "2024-01-18", |
|   "PROVINCE": "Manitoba"        |
| }                               |
+---------------------------------+

This is another example using *. In this case, attribute names are not specified, so Snowflake uses COLUMN1, COLUMN2, and so on:

SELECT OBJECT_CONSTRUCT(*) FROM VALUES(1,'x'), (2,'y');
Copy
+---------------------+
| OBJECT_CONSTRUCT(*) |
|---------------------|
| {                   |
|   "COLUMN1": 1,     |
|   "COLUMN2": "x"    |
| }                   |
| {                   |
|   "COLUMN1": 2,     |
|   "COLUMN2": "y"    |
| }                   |
+---------------------+

This example uses SQL NULL and the string 'null':

SELECT OBJECT_CONSTRUCT(
  'Key_One', PARSE_JSON('NULL'), 
  'Key_Two', NULL, 
  'Key_Three', 'null') AS obj;
Copy
+-----------------------+
| OBJ                   |
|-----------------------|
| {                     |
|   "Key_One": null,    |
|   "Key_Three": "null" |
| }                     |
+-----------------------+

OBJECT_CONSTRUCT supports expressions and queries to add, modify, or omit values from the JSON object.

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;
Copy
+---------------------------------------+
| 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                      |
| }                                     |
+---------------------------------------+
Language: English