Categories:

Semi-structured Data Functions (Array/Object)

OBJECT_CONSTRUCT_KEEP_NULL

Returns an OBJECT constructed from the arguments.

See also:

OBJECT_CONSTRUCT

Syntax

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

OBJECT_CONSTRUCT_KEEP_NULL( * )

Returns

The data type of the returned value is OBJECT.

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(*),
       OBJECT_CONSTRUCT_KEEP_NULL(*)
    FROM demo_table_1;
+----------------------------------+----------------------------------+
| OBJECT_CONSTRUCT(*)              | OBJECT_CONSTRUCT_KEEP_NULL(*)    |
|----------------------------------+----------------------------------|
| {                                | {                                |
|   "CREATED_DATE": "2020-01-18",  |   "CREATED_DATE": "2020-01-18",  |
|   "PROVINCE": "Manitoba"         |   "PROVINCE": "Manitoba"         |
| }                                | }                                |
| {                                | {                                |
|   "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-20"   |   "CREATED_DATE": "2020-01-20",  |
| }                                |   "PROVINCE": null               |
|                                  | }                                |
+----------------------------------+----------------------------------+

For examples that use the closely-related function OBJECT_CONSTRUCT, see OBJECT_CONSTRUCT.