Categories:: Semi-structured and structured data functions (Array/Object)

OBJECT_CONSTRUCT¶

Returns an OBJECT constructed from the arguments.

See also:: OBJECT_CONSTRUCT_KEEP_NULL

Syntax¶

OBJECT_CONSTRUCT( [<key>, <value> [, <key>, <value> , ...]] )

OBJECT_CONSTRUCT(*)

Copy

Arguments¶

key

The key in a key-value pair. Each key is a VARCHAR value.

value

The value that is associated with the key. The value can be any data type.

*

When invoked with an asterisk (wildcard), the OBJECT value is constructed from the specified data using the attribute names as keys and the associated values as values. See the examples below.

When you pass a wildcard to the function, you can qualify the wildcard with the name or alias for the table. For example, to pass in all of the columns from the table named mytable, specify the following:

(mytable.*)

Copy

You can also use the ILIKE and EXCLUDE keywords for filtering:

ILIKE filters for column names that match the specified pattern. Only one pattern is allowed. For example:
```
(* ILIKE 'col1%')
```
Copy
EXCLUDE filters out column names that don’t match the specified column or columns. For example:
```
(* EXCLUDE col1)

(* EXCLUDE (col1, col2))
```
Copy

Qualifiers are valid when you use these keywords. The following example uses the ILIKE keyword to filter for all of the columns that match the pattern col1% in the table mytable:

(mytable.* ILIKE 'col1%')

Copy

The ILIKE and EXCLUDE keywords can’t be combined in a single function call.

You can also specify the wildcard in an object constant.

For this function, the ILIKE and EXCLUDE keywords are valid only in a SELECT list or GROUP BY clause.

For more information about the ILIKE and EXCLUDE keywords, see the “Parameters” section in SELECT.

Returns¶

Returns a value of type OBJECT.

Usage notes¶

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 string that is not NULL as the key and a JSON null as the value (i.e. PARSE_JSON('NULL')) is not omitted. For more information, see VARIANT null.
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 example shows how to construct a simple object:

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

Copy

+--------------------------------------------------+
| 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 OR REPLACE TABLE demo_table_1 (province VARCHAR, created_date DATE);
INSERT INTO demo_table_1 (province, created_date) VALUES
  ('Manitoba', '2024-01-18'::DATE),
  ('Alberta', '2024-01-19'::DATE);

Copy

SELECT province, created_date
  FROM demo_table_1
  ORDER BY province;

Copy

+----------+--------------+
| PROVINCE | CREATED_DATE |
|----------+--------------|
| Alberta  | 2024-01-19   |
| Manitoba | 2024-01-18   |
+----------+--------------+

SELECT OBJECT_CONSTRUCT(*) AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

Copy

+---------------------------------+
| OC                              |
|---------------------------------|
| {                               |
|   "CREATED_DATE": "2024-01-19", |
|   "PROVINCE": "Alberta"         |
| }                               |
| {                               |
|   "CREATED_DATE": "2024-01-18", |
|   "PROVINCE": "Manitoba"        |
| }                               |
+---------------------------------+

This example uses * and includes the ILIKE keyword to filter the output:

SELECT OBJECT_CONSTRUCT(* ILIKE 'prov%') AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

Copy

+--------------------------+
| OC                       |
|--------------------------|
| {                        |
|   "PROVINCE": "Alberta"  |
| }                        |
| {                        |
|   "PROVINCE": "Manitoba" |
| }                        |
+--------------------------+

This example uses * and includes the EXCLUDE keyword to filter the output:

SELECT OBJECT_CONSTRUCT(* EXCLUDE province) AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

Copy

+--------------------------------+
| OC                             |
|--------------------------------|
| {                              |
|   "CREATED_DATE": "2024-01-18" |
| }                              |
| {                              |
|   "CREATED_DATE": "2024-01-19" |
| }                              |
+--------------------------------+

This example is equivalent to the previous example, but it uses an object constant instead of the OBJECT_CONSTRUCT function:

SELECT {* EXCLUDE province} AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

Copy

+--------------------------------+
| OC                             |
|--------------------------------|
| {                              |
|   "CREATED_DATE": "2024-01-18" |
| }                              |
| {                              |
|   "CREATED_DATE": "2024-01-19" |
| }                              |
+--------------------------------+

This is another example using *. In this case, attribute names are not specified, so Snowflake uses COLUMN1, COLUMN2, and so on:

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

Copy

+---------------------+
| 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;

Copy

+-----------------------+
| 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)
  )  AS json_object
  FROM demo_table_1;

Copy

+---------------------------------------+
| JSON_OBJECT                           |
|---------------------------------------|
| {                                     |
|   "created_date_seconds": 1705536000, |
|   "dataset_size": 2,                  |
|   "distinct_province": 2,             |
|   "foo": 1234567                      |
| }                                     |
| {                                     |
|   "created_date_seconds": 1705622400, |
|   "dataset_size": 2,                  |
|   "distinct_province": 2,             |
|   "foo": 1234567                      |
| }                                     |
+---------------------------------------+