Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/object_construct_keep_null below:

Website Navigation

• Reference
• Function and stored procedure reference
• Semi-structured and structured data

OBJECT_CONSTRUCT_KEEP_NULL | Snowflake Documentation

Categories:

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

Returns an OBJECT constructed from the arguments that retains key-values pairs with NULL values.

See also:

OBJECT_CONSTRUCT

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

OBJECT_CONSTRUCT_KEEP_NULL(*)

Copy

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:

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:

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

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.

The data type of the returned value is OBJECT.

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

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;

Copy

+-------------------+-------------------+------------------+
| 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):

CREATE TABLE demo_table_1_with_nulls (province VARCHAR, created_date DATE);
INSERT INTO demo_table_1_with_nulls (province, created_date) VALUES
  ('Manitoba', '2024-01-18'::DATE),
  ('British Columbia', NULL),
  ('Alberta', '2024-01-19'::DATE),
  (NULL, '2024-01-20'::DATE);

Copy

SELECT *
  FROM demo_table_1_with_nulls
  ORDER BY province;

Copy

+------------------+--------------+
| PROVINCE         | CREATED_DATE |
|------------------+--------------|
| Alberta          | 2024-01-19   |
| British Columbia | NULL         |
| Manitoba         | 2024-01-18   |
| NULL             | 2024-01-20   |
+------------------+--------------+

SELECT OBJECT_CONSTRUCT(*) AS oc,
       OBJECT_CONSTRUCT_KEEP_NULL(*) AS oc_keep_null
  FROM demo_table_1_with_nulls
  ORDER BY oc_keep_null['PROVINCE'];

Copy

+----------------------------------+----------------------------------+
| OC                               | OC_KEEP_NULL                     |
|----------------------------------+----------------------------------|
| {                                | {                                |
|   "CREATED_DATE": "2024-01-19",  |   "CREATED_DATE": "2024-01-19",  |
|   "PROVINCE": "Alberta"          |   "PROVINCE": "Alberta"          |
| }                                | }                                |
| {                                | {                                |
|   "PROVINCE": "British Columbia" |   "CREATED_DATE": null,          |
| }                                |   "PROVINCE": "British Columbia" |
|                                  | }                                |
| {                                | {                                |
|   "CREATED_DATE": "2024-01-18",  |   "CREATED_DATE": "2024-01-18",  |
|   "PROVINCE": "Manitoba"         |   "PROVINCE": "Manitoba"         |
| }                                | }                                |
| {                                | {                                |
|   "CREATED_DATE": "2024-01-20"   |   "CREATED_DATE": "2024-01-20",  |
| }                                |   "PROVINCE": null               |
|                                  | }                                |
+----------------------------------+----------------------------------+

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4