- Categories:
Semi-structured Data Functions (Array/Object)
OBJECT_CONSTRUCT_KEEP_NULL¶
Returns an OBJECT constructed from the arguments.
- See also:
Syntax¶
OBJECT_CONSTRUCT_KEEP_NULL( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
OBJECT_CONSTRUCT_KEEP_NULL( * )
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 is NULL (i.e. SQL NULL), the key-value pair is omitted from the resulting object. However, if the value is null, then the key-value pair is kept.
The constructed object does not necessarily preserve the original order of the key-value pairs.
Examples¶
This example shows the difference between OBJECT_CONSTRUCT and OBJECT_CONSTRUCT_KEEP_NULL:
SELECT OBJECT_CONSTRUCT('key_1', 'one', 'key_2', NULL) AS WITHOUT_KEEP_NULL, OBJECT_CONSTRUCT_KEEP_NULL('key_1', 'one', 'key_2', NULL) AS KEEP_NULL_1, OBJECT_CONSTRUCT_KEEP_NULL('key_1', 'one', NULL, 'two') AS KEEP_NULL_2 ; +-------------------+-------------------+------------------+ | WITHOUT_KEEP_NULL | KEEP_NULL_1 | KEEP_NULL_2 | |-------------------+-------------------+------------------| | { | { | { | | "key_1": "one" | "key_1": "one", | "key_1": "one" | | } | "key_2": null | } | | | } | | +-------------------+-------------------+------------------+
The following example also shows the difference between OBJECT_CONSTRUCT and OBJECT_CONSTRUCT_KEEP NULL, but this example uses a small table (which is shown prior to the query):
SELECT * FROM demo_table_1 ORDER BY province; +------------------+--------------+ | PROVINCE | CREATED_DATE | |------------------+--------------| | Alberta | 2020-01-19 | | British Columbia | NULL | | Manitoba | 2020-01-18 | | NULL | 2020-01-20 | +------------------+--------------+SELECT OBJECT_CONSTRUCT(*) AS oc, OBJECT_CONSTRUCT_KEEP_NULL(*) AS oc_keep_null FROM demo_table_1 ORDER BY oc_keep_null['PROVINCE']; +----------------------------------+----------------------------------+ | OC | OC_KEEP_NULL | |----------------------------------+----------------------------------| | { | { | | "CREATED_DATE": "2020-01-19", | "CREATED_DATE": "2020-01-19", | | "PROVINCE": "Alberta" | "PROVINCE": "Alberta" | | } | } | | { | { | | "PROVINCE": "British Columbia" | "CREATED_DATE": null, | | } | "PROVINCE": "British Columbia" | | | } | | { | { | | "CREATED_DATE": "2020-01-18", | "CREATED_DATE": "2020-01-18", | | "PROVINCE": "Manitoba" | "PROVINCE": "Manitoba" | | } | } | | { | { | | "CREATED_DATE": "2020-01-20" | "CREATED_DATE": "2020-01-20", | | } | "PROVINCE": null | | | } | +----------------------------------+----------------------------------+
For examples that use the closely-related function OBJECT_CONSTRUCT, see OBJECT_CONSTRUCT.