CODEWITHSUNDEEP
sqlgoogle-bigquery
Harish Nandoliya
Harish Nandoliya

Reputation: 101

Big query join on comma separated values

I have the following table structure in BigTable.

enter image description here

enter image description here

And I want below output by joining based on comma values.

enter image description here

MySQL has find_in_set function which does similar thing, but how can I do in bigquery?

Upvotes: 7

Views: 3144

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Consider below option

#standardSQL
select id, product_name, 
  (select string_agg(category_name)
  from unnest(split(categories)) as cat_id
  join `project.dataset.categories` 
  on cat_id = cast(id as string)
  ) as categories
from `project.dataset.products`

for the sample data you provided in question - it returns

enter image description here

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Fix your data model! Don't store multiple values in a string column, particularly when BQ has much better ways to store the data -- such as arrays.

You can do this -- by converting to arrays and back to strings:

select t.*,
       (select string_agg(c.category_name order by n, ',')
        from unnest(split(t.categories, ',')) category with offset n join
             categories c
             on category = c.id
       ) as categories
from t;

That said, you should at least use array_agg() for the results.

Upvotes: -2

Related Questions