Categories:

Semi-structured Data Functions (Array/Object)

ARRAY_COMPACTΒΆ

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

SyntaxΒΆ

ARRAY_COMPACT( <array1> )
Copy

ArgumentsΒΆ

array1

The source array.

Usage NotesΒΆ

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

ExamplesΒΆ

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
Language: English