Wsrtjtyk

I need to filter by more nested data in bigquery and I am only able to filter with my query by one.

Basically i need this:

SELECT item_id FROM table WHERE item_id IS NOT NULL AND page_id = '23784'

Is such thing possible?

I have data in bigquery like below, page_id doesn't have to exist:

| row | date | event      | params.key    | params.value |

-------------------------------------------------------

| 1   | 2018 | screenShow | item_id       | 1            |

                          | page_id       | 23784        |

                          | irrelevant_id | 5            |

| 2   | 2018 | screenShow | item_id       | 2            |

                          | irrelevant_id | 7            |

My query is:

SELECT param.value

FROM `table`,

UNNEST(params) AS param

WHERE

    event = 'screenShow'

    AND param.key = 'item_id'

but this obviously works only for one key and i don't know how to add the page_id part.
Thanks.

All item_ids where item_id is not null and page_id is 23784

Below is for BigQuery Standard SQL

#standardSQL

SELECT 

  (SELECT value FROM UNNEST(params) param WHERE key = 'item_id') item_id

FROM `project.dataset.table`

WHERE (

  SELECT COUNT(1) 

  FROM UNNEST(params) param 

  WHERE param = ('page_id', 23784)

  OR key = 'item_id'

  ) = 2  

You can test, play with above using dummy data as below

#standardSQL

WITH `project.dataset.table` AS (

  SELECT 2018 dt, 'screenShow' event, 

    [STRUCT<key STRING, value INT64>('item_id', 1), ('page_id', 23784), ('irrelevant_id', 5)] params UNION ALL

  SELECT 2018 dt, 'screenShow' event, 

    [STRUCT<key STRING, value INT64>('item_id', 2), ('irrelevant_id', 7)] params UNION ALL

  SELECT 2018 dt, 'screenShow' event, 

    [STRUCT<key STRING, value INT64>('item_id2', 1), ('page_id', 23784), ('irrelevant_id', 5)] params 

)

SELECT 

  (SELECT value FROM UNNEST(params) param WHERE key = 'item_id') item_id

FROM `project.dataset.table`

WHERE (

  SELECT COUNT(1) 

  FROM UNNEST(params) param 

  WHERE param = ('page_id', 23784)

  OR key = 'item_id'

  ) = 2

with result

Row item_id  

1   1       

Obviously, if instead just list of item_id's you need whole row - you just use SELECT * as in below

#standardSQL

SELECT *

FROM `project.dataset.table`

WHERE (

  SELECT COUNT(1) 

  FROM UNNEST(params) param 

  WHERE param = ('page_id', 23784)

  OR key = 'item_id'

  ) = 2  

in this case you will get

| row | date | event      | params.key    | params.value |

-------------------------------------------------------

| 1   | 2018 | screenShow | item_id       | 1            |

                          | page_id       | 23784        |

                          | irrelevant_id | 5            |

Try the following:

SELECT

  (SELECT x.value FROM UNNEST(params) AS x WHERE x.key = 'item_id') AS item_id

FROM

  `your_dataset.your_table`

WHERE

  EXISTS (

  SELECT

    *

  FROM

    UNNEST(params) AS x

  JOIN

    UNNEST (params) AS y

  WHERE

    x.key = 'item_id'

    AND x.value IS NOT NULL

    AND y.key = 'page_id'

    AND y.value=23784)

Well, you could do:

select t.*

from t

where exists (select 1 from unnest(params) p where p.key = 'item_id' and p.value is not null) and

      exists (select 1 from unnest(params) p where p.key = 'page_id' and p.value = 23784);