I need to get first 5 elements from concatenated array in BigQuery
SELECT id, ARRAY_CONCAT_AGG(a LIMIT 5) FROM (
SELECT 1 as id, ARRAY [6,7,8,9] a
UNION ALL
SELECT 1 ,ARRAY [2,3,4,5]
)
GROUP BY 1
This query looks like correct but returns full array ignoring LIMIT.
What I do wrong?
According to the ARRAY_CONCAT_AGG docs, the LIMIT option applies to the number of input arrays, not to the number of elements in the arrays.
There does not seem to be a builtin BigQuery function for returning a slice of an array. So, re-using some of this answer, the following worked for me in standard SQL to return just the first 5 elements from the concatenated array:
SELECT id, ARRAY(SELECT ix FROM UNNEST(arr) ix
WITH OFFSET INDEX
WHERE index BETWEEN 0 AND 4 ORDER BY index)
FROM ( SELECT id,
ARRAY_CONCAT_AGG(a) AS arr
FROM ( SELECT 1 as id, ARRAY [6,7,8,9] a
UNION ALL
SELECT 1 ,ARRAY [2,3,4,5] )
GROUP BY id)
slice in JS)