Categories:

Semi-structured and 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> )

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.

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

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

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

Execute the query:

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