BigQuery Array Element Multiplicaton

Question

Noob question here:

I have a BQ table with a nested structure containing 2 arrays, x and y. I want to create either a new array of xy or have the 2 arrays unnested and create a flattened table of x, y & xy values.

WITH
  tests AS (
  SELECT
  [  struct("test1" AS test,
        [0.1576,0.9706,0.9572,0.4854,0.8003,0.1419,0.4218,0.9157,0.7922,0.9595] AS x,
        [11,12,13,14,15,16,17,18,19,20] AS y),
    struct("test2" AS test,
        [0.8147,0.9058,0.1270,0.9134,0.6324,0.0975,0.2785,0.5469,0.9575, 0.9649] AS x,
        [11,12,13,14,15,16,17,18,19,20] AS y),
   struct("test3" AS test,
        [0.6557,0.0357,0.8491,0.9340,0.6787,0.7577,0.7431,0.3922,0.6555,0.1712] AS x,
        [11,12,13,14,15,16,17,18,19,20] AS y)
   ] AS measurements)


SELECT
  test, x,y
FROM
  tests t, unnest(t.measurements)

I would like extend the above query to get a flattened table with test number, x, y and x*y like this, or something similar:

tests	x	y	x*y
test1	0.1576	11	1.7366
test1	0.9706	12	11.6472
test1	0.9572	13	12.4436
test1	0.4854	14	6.7956
...	...	...	...
test3	0.1712	20	3.4240

How to create a Minimal, Complete, and Verifiable example.

Mikhail Berlyant
– Mikhail Berlyant

2022-03-16 16:33:21 +00:00
Commented Mar 16, 2022 at 16:33 — Mikhail Berlyant
– Mikhail Berlyant, Commented Mar 16, 2022 at 16:33

Mikhail Berlyant · Accepted Answer · 2022-03-17 17:28:23Z

2

Consider below approach

select test, x, y, round(x*y, 4) as x_multiply_y
from tests, unnest(measurements),
unnest(x) x with offset 
join unnest(y) y with offset 
using(offset)

if applied to sample data in your question - output is

answered Mar 17, 2022 at 17:28

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

2 Comments

MartinD Over a year ago

Thanks Mikhail, that works for the example. If I extend it to give xyz data by adding on another cross join to an additional column it also works but when the array lengths of x, y & z get very big (10000+ elements) BQ struggles and complains of slots and large amounts of shuffled data. Any thoughts on alternatives or methods to just extraxt the x,y & z arrays and add them as new columns to the table?

Mikhail Berlyant Over a year ago

yo can try to materialize intermediate steps. for example first - from tests, unnest(measurements) into table1, then unnest(x) x with offset and unnest(y) y with offset into respectively table2 and table 3 and finally joint those last two tables ...

Collectives™ on Stack Overflow

BigQuery Array Element Multiplicaton

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related