BigQuery: Subquery with UNION as ARRAY

Question

I have the following two example tables

Orders Table

order_id	linked_order1	linked_order2
1001	L005	null
1002	null	null
1003	L006	L007

Invoices Table

order_id	linked_order_id	charge
1001	null	4.27
1002	null	9.82
1003	null	7.42
null	L005	2.12
null	L006	1.76
null	L007	3.20

I need to join these so the charges of all the orders (linked and otherwise) can be shown as part of the single order row. My desired output is something like this.

Desired Output

order_id	linked_order1	linked_order2	invoices.charge	invoices.order_id	invoices.linked_order_id
1001	L005	null	4.27	1001	null
			2.12	null	L005
1002	null	null	9.82	null	null
1003	L006	L007	7.42	null	null
			1.76	null	L006
			3.20	null	L007

I can manage to get the main order into the table as follows.

SELECT
  orders,
  ARRAY(
  SELECT AS STRUCT * FROM `invoices_table` WHERE order=orders.order_id) AS invoice
FROM
  `orders_table` AS orders

I can run a separate query to union all of the invoice results into a single table for given order ids but I can't combine this with the above query with out getting errors.

Something like this...

SELECT
  orders,
  ARRAY(
  SELECT AS STRUCT * FROM 
(SELECT * FROM `invoices_table` WHERE order=orders.order_id
    UNION ALL SELECT * FROM `invoices_table` WHERE linked_order_id=orders.linked_order1
    UNION ALL SELECT * FROM `invoices_table` WHERE linked_order_id=orders.linked_order2)
) AS invoice
FROM
  `orders_table` AS orders

But this gives me the correlated subqueries error.

[Update]

This is much simpler than I thought. The following query gives me what I was after.

SELECT
  orders,
  ARRAY(
  SELECT AS STRUCT * FROM `invoices_table` WHERE order=orders.order_id OR linked_order_id IN(orders.linked_order1, orders.linked_order2)) AS invoice
FROM
  `orders_table` AS orders

Jaytiger · Accepted Answer · 2022-09-05 14:13:43Z

1

Using CROSS JOINS,

SELECT o.*, ARRAY_AGG(i) invoices
  FROM Orders o, Invoices i 
 WHERE o.order_id = i.order_id 
    OR i.linked_order_id IN (o.linked_order1, o.linked_order2)
 GROUP BY 1, 2, 3;

Query results

[UPDATE]

Sometimes the query using OR conditions in WHERE clause might show poor perfomrance in large dataset. In that case you may try below query instead that generates same result.

SELECT o.*, ARRAY_AGG(i) invoices FROM (
  SELECT o, i FROM Orders o JOIN Invoices i USING (order_id)
   UNION ALL
  SELECT o, i FROM Orders o JOIN Invoices i ON i.linked_order_id IN (o.linked_order1, o.linked_order2)
) GROUP BY 1, 2, 3;

edited Sep 5, 2022 at 14:13

answered Sep 5, 2022 at 13:56

Jaytiger

12.3k2 gold badges7 silver badges16 bronze badges

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

3 Comments

mister_b Over a year ago

This works for the example I gave, thanks. My actual usage is a bit more complex (the linked orders are coming from another join) but you have set me down a better direction than what I was going so thank you.

Jaytiger Over a year ago

@mister_b, kindly check updated answer as well.

mister_b Over a year ago

Actually found a much simpler solution, see my update.

Samuel · Accepted Answer · 2022-09-05 12:21:46Z

For the desired output table, the full outer join is the right command.

with tblA as (Select order_id, 1 linked_order1, 2   linked_order2, from unnest([1,2,3]) order_id),
tblB as (Select order_id, 109.99 charge from unnest([3,4,5]) order_id
union all select null  order_id, * from unnest([50.1,29.99]) charge
)

Select *  
  from tblA
  full join tblB
  using(order_id)

For your setting, there is the need to have several joining conditions. Therefore, the first table is used three times, for each joining key.

with tblA as (Select order_id, "L05" linked_order1, "L2"    linked_order2, from unnest(["1","2","3"]) order_id),
tblB as (Select order_id, null linked_order_id, 109.99 charge from unnest(["3","4","5"]) order_id
union all select null  order_id, "L05" , * from unnest([50.1,29.99]) charge
)

Select A.order_id,linked_order1,linked_order2, array_agg(struct(tblB.order_id,linked_order_id,charge))
  from 
  (
    Select * from tblA, unnest([order_id,linked_order1,linked_order2]) as tmp_id
  ) A
  full join tblB
  on tmp_id = ifnull(tblB.order_id,linked_order_id)
  where charge is not null #or tmp_id=A.order_id
  group by 1,2,3

Collectives™ on Stack Overflow

BigQuery: Subquery with UNION as ARRAY

Orders Table

Invoices Table

Desired Output

[Update]

2 Answers 2

Query results

[UPDATE]

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Orders Table

Invoices Table

Desired Output

[Update]

2 Answers 2

Query results

[UPDATE]

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related