- Categories:
Semi-structured and Structured Data Functions (Array/Object)
OBJECT_CONSTRUCT¶
Returns an OBJECT constructed from the arguments.
- See also:
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).
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 is 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’)) is not omitted.
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 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 province, created_date FROM demo_table_1 ORDER BY province; +----------+--------------+ | PROVINCE | CREATED_DATE | |----------+--------------| | Alberta | 2020-01-19 | | Manitoba | 2020-01-18 | +----------+--------------+SELECT OBJECT_CONSTRUCT(*) AS oc FROM demo_table_1 ORDER BY oc['PROVINCE']; +---------------------------------+ | OC | |---------------------------------| | { | | "CREATED_DATE": "2020-01-19", | | "PROVINCE": "Alberta" | | } | | { | | "CREATED_DATE": "2020-01-18", | | "PROVINCE": "Manitoba" | | } | +---------------------------------+
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 | | } | +-------------------------------------------------------------------------------+