Categories:: Semi-structured and Structured Data Functions (Array/Object)

ARRAY_CONSTRUCT_COMPACT¶

Returns an array constructed from zero, one, or more inputs; the constructed array omits any NULL input values.

See also:: ARRAY_CONSTRUCT

Syntax¶

ARRAY_CONSTRUCT_COMPACT( [ <expr1> ] [ , <expr2> [ , ... ] ] )

Copy

Arguments¶

expr#: These are the input expressions to evaluate; the resulting values are put into the array. The expressions do not all need to evaluate to the same data type.

Returns¶

The data type of the returned value is ARRAY.

Usage Notes¶

SQL NULL values are skipped when building the result array, resulting in a compacted (i.e. dense) array.

Examples¶

Construct a basic dense array consisting of different data types:

SELECT ARRAY_CONSTRUCT_COMPACT(null,'hello',3::double,4,5);
+-----------------------------------------------------+
| ARRAY_CONSTRUCT_COMPACT(NULL,'HELLO',3::DOUBLE,4,5) |
|-----------------------------------------------------|
| [                                                   |
|   "hello",                                          |
|   3.000000000000000e+00,                            |
|   4,                                                |
|   5                                                 |
| ]                                                   |
+-----------------------------------------------------+

Copy