OBJECT_CONSTRUCT function in Snowflake - SQL Syntax and Examples

OBJECT_CONSTRUCT Description

OBJECT_CONSTRUCT function Syntax

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

OBJECT_CONSTRUCT( * )

OBJECT_CONSTRUCT function Examples

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

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

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

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

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 mytable),
  'distinct_col1', (SELECT COUNT(DISTINCT col1) FROM mytable),
  'created_dt_seconds', extract(epoch_seconds, created_dt),
  'created_dt_millis', extract(epoch_millisecond, created_dt),
)
FROM mytable;

OBJECT_CONSTRUCT function Usage

The function either accepts 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.

If key or value are 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.

Convert your code online to Snowflake