Is it possible to UNNEST an array created using ARRAY_CONCAT_AGG

Question

In BigQuery, I have the following query:

SELECT
  `order`.source AS order_source,
  `order`.key AS order_key,
  ANY_VALUE(`order`.date) AS order_date,
  order_product.key AS key,
  ANY_VALUE(order_product.sku) AS sku,
  ANY_VALUE(order_product.name) AS name,
  ANY_VALUE(order_product.quantity) AS quantity,
  ANY_VALUE(order_product.subtotal) AS revenue,
  ARRAY_CONCAT_AGG(moved_quants) AS moved_quants
 FROM 
  `analytics.spr.stock_move_shipments`
 GROUP BY
  `order`.source,
  `order`.key,
  order_product.key

Each row in analytics.spr.stock_move_shipments has repeated moved_quants field. As you can see in the query, I am grouping rows together and creating an array of moved_quants for the group using ARRAY_CONCAT_AGG.

However, what I would really like to do is perform some aggregation on those moved_quants and display those results in each row. I thought I could just change the last line of the SELECT statement to something like this:

SELECT
  `order`.source AS order_source,
  `order`.key AS order_key,
  ANY_VALUE(`order`.date) AS order_date,
  order_product.key AS key,
  ANY_VALUE(order_product.sku) AS sku,
  ANY_VALUE(order_product.name) AS name,
  ANY_VALUE(order_product.quantity) AS quantity,
  ANY_VALUE(order_product.subtotal) AS revenue,
  (SELECT SUM(t1.inventory_value) FROM UNNEST(ARRAY_CONCAT_AGG(moved_quants)) t1) AS inventory_value
 FROM 
  `analytics.spr.stock_move_shipments`
 GROUP BY
  `order`.source,
  `order`.key,
  order_product.key

However, I am receiving the following error:

Aggregate function ARRAY_CONCAT_AGG not allowed in UNNEST

Why is this not allowed? Shouldn't I be able to simply UNNEST the array created by ARRAY_CONCAT_AGG? Is there some better way to accomplish this?

Mikhail Berlyant · Accepted Answer · 2019-08-09 01:04:19Z

2

Below is the simplest option for you - just to transform lightly what you already have to work as you want and avoid UNNESTing just aggregated array in same query - so you just separate it

#standardSQL
SELECT * EXCEPT(moved_quants), 
  (SELECT SUM(inventory_value) FROM UNNEST(moved_quants)) AS inventory_value
FROM (
SELECT
  `order`.source AS order_source,
  `order`.key AS order_key,
  ANY_VALUE(`order`.date) AS order_date,
  order_product.key AS key,
  ANY_VALUE(order_product.sku) AS sku,
  ANY_VALUE(order_product.name) AS name,
  ANY_VALUE(order_product.quantity) AS quantity,
  ANY_VALUE(order_product.subtotal) AS revenue,
  ARRAY_CONCAT_AGG(moved_quants) AS moved_quants
 FROM 
  `analytics.spr.stock_move_shipments`
 GROUP BY
  `order`.source,
  `order`.key,
  order_product.key
)

note: typed on fly and not tested ...

But even simpler solution is below

SELECT
  `order`.source AS order_source,
  `order`.key AS order_key,
  ANY_VALUE(`order`.date) AS order_date,
  order_product.key AS key,
  ANY_VALUE(order_product.sku) AS sku,
  ANY_VALUE(order_product.name) AS name,
  ANY_VALUE(order_product.quantity) AS quantity,
  ANY_VALUE(order_product.subtotal) AS revenue,
  SUM((SELECT SUM(inventory_value) FROM UNNEST(moved_quants))) AS inventory_value
 FROM 
  `analytics.spr.stock_move_shipments`
 GROUP BY
  `order`.source,
  `order`.key,
  order_product.key

I would definitely went with this last one!

edited Aug 9, 2019 at 1:04

answered Aug 9, 2019 at 0:41

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

6 Comments

flyingL123 Over a year ago

Thanks! Last one is great, but what would you do if you actually needed multiple values from the quants? I was just using one for simplicity, but really I need to SUM(inventory_value), SUM(tariff_amount), and some other fields from within the array. The best I could do was your first solution but using SELECT AS STRUCT to get all the fields I want. Do you have a better idea?

Mikhail Berlyant Over a year ago

yes, in this case you can use first solution with AS STRUCT ! I think this would be the most optimal approach in case of multiple SUMs, while second one - good for just one SUM

flyingL123 Over a year ago

I'm not sure the second solution works. I'm getting

Each function argument is an expression, not a query; to use a query as an expression, the query must be wrapped with additional parentheses to make it a scalar subquery expression

Mikhail Berlyant Over a year ago

i just tested second one with simplified data and it works for me! in any case you ahve first one - right! as of second - just add extra () as in SUM((SELECT SUM(inventory_value) FROM UNNEST(moved_quants))) AS inventory_value

Mikhail Berlyant Over a year ago

as I said - i was free-typing - so looks like i just missed those extra brackets - when i did the quick test that I mentioned above - I actually had those extras in - that is why it worked - so I just updated answer :o) thank you for checking!

|

Collectives™ on Stack Overflow

Is it possible to UNNEST an array created using ARRAY_CONCAT_AGG

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related