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