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