Array type in table?

Question

I have data with a single column of and array of IDs that are not in JSON format.

"100,101,102"

I would like to be able to query rows with something like

WHERE "101" IN idarray

but it appears that the ARRAY type is not supported as a column type.

Is there an approach in BigQuery where an array or delimited string can be queried for containing an element?

For example in PostgreSQL I can use the array contains element syntax @>

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-08-08T19:40:19

SELECT *
FROM (
SELECT idarray FROM
  (SELECT "100,101,102" AS idarray),
  (SELECT "200,201,202,1010" AS idarray),
) as YourTable
WHERE "," + idarray + "," CONTAINS "," + "101" + ","

Same as above but "adopted" for BigQuery Standard SQL

WITH YourTable AS (
  SELECT "100,101,102" AS idarray UNION ALL
  SELECT "200,201,202,1010" AS idarray
)
SELECT *
FROM YourTable
WHERE CONCAT(",", idarray, ",") LIKE CONCAT("%,", "101", ",%")

or

WITH YourTable AS (
  SELECT "100,101,102" AS idarray UNION ALL
  SELECT "200,201,202,1010" AS idarray
)
SELECT *
FROM YourTable
WHERE REGEXP_CONTAINS(idarray, r"\b101\b")

or ("pure" Standard SQL style)

WITH YourTable AS (
  SELECT "100,101,102" AS idarray UNION ALL
  SELECT "200,201,202,1010" AS idarray
)
SELECT idarray
FROM YourTable, UNNEST(SPLIT(idarray)) AS id1
WHERE id1 = "101"

Array type in table?

1 Answers