filtering array elements from string columns bigquery

Question

I have two tables

first table

Row  feature_groups           array_words   
1    chat seller              chat
2    product picture/video    product picture
                              video

second table

Row  events                                       
1    product picture is perfect     
2    video on product babyboy
3    chat bot creation
4    image gorilla

I want to filter out the events on the basis of array_words

Result should be -

Row  events                         feature_groups
1    product picture is perfect     product picture/video
2    video on product babyboy       product picture/video
3    chat bot creation              chat seller

Any help with biqquery can be of great help

how do you pretend to join both tables? do you have any ID that matches both? — Aleix CC
– Aleix CC, Commented Sep 16, 2021 at 8:34

Mikhail Berlyant · Accepted Answer · 2021-09-16 17:31:53Z

3

Consider below approach

select events, feature_groups from (
  select *, 
    (
      select count(1)
      from unnest(split(t2.events, ' ')) word
      join t1.array_words word
      using(word)
    ) match
  from table_2 t2, table_1 t1
  where true
  qualify row_number() over(partition by events order by match desc) = 1
  and match > 0
)

if applied to sample data in your question

Table_1

and Table_2

output is

answered Sep 16, 2021 at 17:31

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Berry Over a year ago

thanks , that solves it

Collectives™ on Stack Overflow

filtering array elements from string columns bigquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related