BigQuery Join Array in standard SQL

Question

I am using standard SQL and I have table Order:

and I am trying to join it with table MenuItem

on Order item_ids array and MenuItem __id__ integer column and get array of MenuItem prices, but I am getting an error:

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

How to avoid this error?

Query:

WITH menu_items AS 
( 
    SELECT
        __id__,
        price
    FROM
        `potykion.MenuItem`
)
SELECT
    *, 
    ARRAY(
        SELECT 
           price
        FROM 
           UNNEST(item_ids) AS id
        JOIN 
            menu_items 
        ON 
            id = menu_items.__id__
    ) 
FROM 
    `potykion.Order`

What is item_details? I don't see it in the sample data for the Orders table. — Elliott Brossard
– Elliott Brossard, Commented Aug 10, 2016 at 15:37
i would guess that item_details is the record in one of the table. Still, please provide schema of your tables with few rows example and expected result. - Generically speaking - please edit your question to show a Minimal, Complete, and Verifiable example of the code that you are having problems with, then we can try to help with the specific problem. You can also read How to Ask. — Mikhail Berlyant
– Mikhail Berlyant, Commented Aug 10, 2016 at 18:37
what is expected output? - still not clear - is it order's fields plus total price for all items in order? — Mikhail Berlyant
– Mikhail Berlyant, Commented Aug 11, 2016 at 0:48

Mikhail Berlyant · Accepted Answer · 2016-08-11 05:48:37Z

10

Try below (BigQuery Standard SQL)

WITH Orders AS (
  SELECT 1 AS id, ARRAY[1,2,3] AS item_ids UNION ALL 
  SELECT 2 AS id, ARRAY[4,5] AS item_ids UNION ALL 
  SELECT 3 AS id, ARRAY[1,4,6] AS item_ids 
),
MenuItems AS (
  SELECT 1 AS __id__, 1.1 AS price UNION ALL
  SELECT 2 AS __id__, 1.2 AS price UNION ALL
  SELECT 3 AS __id__, 1.3 AS price UNION ALL
  SELECT 4 AS __id__, 1.4 AS price UNION ALL
  SELECT 5 AS __id__, 1.5 AS price UNION ALL
  SELECT 6 AS __id__, 1.6 AS price UNION ALL
  SELECT 7 AS __id__, 1.7 AS price 
)
SELECT 
  *, 
  ARRAY(
    SELECT price 
    FROM UNNEST(item_ids) AS id 
    JOIN MenuItems 
    ON __id__ = id
  ) AS prices
FROM Orders

Table Orders:

Table MenuItems:

Result:

answered Aug 11, 2016 at 5:48

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

2 Comments

potykion Over a year ago

Well my array creation expression is actually same, I guess I get error, because MenuItems is an another table.

Mikhail Berlyant Over a year ago

Hope my "excercise" helped anyway!

potykion · Accepted Answer · 2016-08-12 04:35:06Z

Solution with join inside array creation expression is correct, but it doesn't work with separate tables. Alternative solution is array aggregation:

WITH Orders AS (
  SELECT 1 AS id, ARRAY[1,2,3] AS item_ids UNION ALL 
  SELECT 2 AS id, ARRAY[4,5] AS item_ids UNION ALL 
  SELECT 3 AS id, ARRAY[1,4,6] AS item_ids 
),
MenuItems AS (
  SELECT 1 AS __id__, 1.1 AS price UNION ALL
  SELECT 2 AS __id__, 1.2 AS price UNION ALL
  SELECT 3 AS __id__, 1.3 AS price UNION ALL
  SELECT 4 AS __id__, 1.4 AS price UNION ALL
  SELECT 5 AS __id__, 1.5 AS price UNION ALL
  SELECT 6 AS __id__, 1.6 AS price UNION ALL
  SELECT 7 AS __id__, 1.7 AS price 
)
SELECT 
  id, ARRAY_AGG(price)
FROM Orders  
  JOIN MenuItems ON __id__ in UNNEST(item_ids)
  GROUP BY id

Collectives™ on Stack Overflow

BigQuery Join Array in standard SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related