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

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

ARRAY_COMPACT | Snowflake Documentation

Categories:

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

Returns a compacted array with missing and null values removed, effectively converting sparse arrays into dense arrays.

ARRAY_COMPACT( <array1> )

Copy

array1

The source array.

• Semi-structured data (e.g. JSON data) can contain explicit null values, which are distinct from SQL NULLs. A null value in semi-structured data indicates a missing value.

• array1 should be either an ARRAY data type or a VARIANT data type containing an array value.

• If the argument is NULL, the result will be NULL.

• When you pass a structured array to the function, the function returns a structured array of the same type.

This example shows how to use ARRAY_COMPACT():

Create a simple table and data:

CREATE TABLE array_demo (ID INTEGER, array1 ARRAY, array2 ARRAY);

Copy

INSERT INTO array_demo (ID, array1, array2) 
    SELECT 2, ARRAY_CONSTRUCT(10, NULL, 30), ARRAY_CONSTRUCT(40);

Copy

Execute the query:

SELECT array1, ARRAY_COMPACT(array1) FROM array_demo WHERE ID = 2;
+--------------+-----------------------+
| ARRAY1       | ARRAY_COMPACT(ARRAY1) |
|--------------+-----------------------|
| [            | [                     |
|   10,        |   10,                 |
|   undefined, |   30                  |
|   30         | ]                     |
| ]            |                       |
+--------------+-----------------------+

Copy

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