Search code examples
google-bigquerynested

How to filter nested column in Bigquery?

I have a bigquery table which looks like below:

enter image description here

The column details says it's of type "REPEATED". So I guess that's the word for nested column, and it indeed looks like one. I would like to filter on this particular NAME column like below:

WITH T AS (
SELECT 
CUST_ID,NAME
FROM Table
)
SELECT * EXCEPT(NAME)
FROM T, UNNEST(NAME)
WHERE NAME LIKE '%VERMA%'

I tried above snippet from the solution to a similar question asked here. But it is throwing me an error:

enter image description here

I tried different things like using IN etc but failing. Does anyone know the correct syntax to use here please?

Solution

  • Oh, you are just missing the alias for your unnest field:

    See if following works:

    WITH T AS (
SELECT 
123 as CUST_ID, ["JOHN", "DOE"]  as NAME
)
SELECT * EXCEPT(NAME)
FROM T, UNNEST(NAME) as name_value
WHERE name_value LIKE '%JOHN%'