Categories:

Semi-structured Data Functions (Array/Object)

OBJECT_CONSTRUCT

Returns an OBJECT constructed from the arguments.

Syntax

OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )

OBJECT_CONSTRUCT( * )

Usage Notes

  • The function accepts either a sequence of zero or more key-value pairs (where keys are strings, and values are of any type) or an asterisk. When invoked with an asterisk, the object is constructed using the attribute names as keys and the associated tuple values as values. See the examples below.

  • If the key or value is NULL (i.e. SQL NULL), the key-value pair will be omitted from the resulting object. A key-value pair consisting of a not-null string as key and a JSON NULL as value (i.e. PARSE_JSON(‘NULL’)) will not be omitted.

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

Examples

This shows how to construct a simple object:

SELECT OBJECT_CONSTRUCT('a',1,'b','BBBB', 'c',null);
+----------------------------------------------+
| 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 TABLE demo_table_1 (province VARCHAR, created_date DATE);
INSERT INTO demo_table_1 (province, created_date) VALUES
    ('Manitoba', '2020-01-18'::DATE),
    ('Alberta', '2020-01-19'::DATE);
SELECT * FROM demo_table_1;
+----------+--------------+
| PROVINCE | CREATED_DATE |
|----------+--------------|
| Manitoba | 2020-01-18   |
| Alberta  | 2020-01-19   |
+----------+--------------+
SELECT OBJECT_CONSTRUCT(*) FROM demo_table_1;
+---------------------------------+
| OBJECT_CONSTRUCT(*)             |
|---------------------------------|
| {                               |
|   "CREATED_DATE": "2020-01-18", |
|   "PROVINCE": "Manitoba"        |
| }                               |
| {                               |
|   "CREATED_DATE": "2020-01-19", |
|   "PROVINCE": "Alberta"         |
| }                               |
+---------------------------------+

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

SELECT OBJECT_CONSTRUCT(*) FROM VALUES(1,'x'), (2,'y');
+---------------------+
| 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;
+-----------------------+
| 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)
    )
    FROM demo_table_1;
+-------------------------------------------------------------------------------+
| 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)              |
|     )                                                                         |
|-------------------------------------------------------------------------------|
| {                                                                             |
|   "created_date_seconds": 1579305600,                                         |
|   "dataset_size": 2,                                                          |
|   "distinct_province": 2,                                                     |
|   "foo": 1234567                                                              |
| }                                                                             |
| {                                                                             |
|   "created_date_seconds": 1579392000,                                         |
|   "dataset_size": 2,                                                          |
|   "distinct_province": 2,                                                     |
|   "foo": 1234567                                                              |
| }                                                                             |
+-------------------------------------------------------------------------------+