CODEWITHSUNDEEP
sqlgoogle-bigquery
denim
denim

Reputation: 463

Join two repeated fields in a BigQuery table

Assume the below table that has two repeated fields (product1 & product2).

Table A

What is the most efficient way to left outer join (on sku) the two repeated fields into one repeated field so that the result looks like this?

enter image description here

Upvotes: 0

Views: 261

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

Consider below approach

select * except(product1, product2),
  array(
    select as struct sku, description, quantity
    from t.product1
    left join t.product2
    using(sku)
  ) product
from data t

If applied to sample data in your question - output is

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Hmmm . . . You can unnest() and then reaggregate:

select t.*,
       (select array_agg(product)
        from (select sku, p2.description, p2.quantity
              from unnest(product1) p1 left join
                   unnest(product2) p2
                   using (sku)
             ) product
       ) as product
from t;

Upvotes: 1

Related Questions