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 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.

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;