Categories:

Semi-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> [ , ... ] ] )

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                                                 |
| ]                                                   |
+-----------------------------------------------------+