ARRAY_COUNT_GLOBAL

Returns the number of elements in the array typed column accumulated over all rows. As such it is an aggregation function.

Syntax

ARRAY_COUNT_GLOBAL(<array>)

Example

For this example, we will create a table array_test as shown below.

CREATE DIMENSION TABLE array_test(array_1 ARRAY(INTEGER));

INSERT INTO
	array_test
VALUES
	([ 1, 2, 3, 4 ]),
	([ 5, 0, 20 ]),
	([ 6, 2, 6 ]),
	([ 9, 10, 13 ]),
	([ 20, 13, 40 ]),
	([ 1 ]);

We can use ARRAY_COUNT_GLOBAL to learn how many total array elements are in all rows.

SELECT
	ARRAY_COUNT_GLOBAL(array_1)
FROM
	array_test;

Returns: 17

If you want to count elements based on specific criteria, you can use the ARRAY_COUNT function with a SUM aggregation as demonstrated below.

SELECT
	SUM(ARRAY_COUNT(x -> x > 3, array_1))
FROM
	array_test;

Returns: 11