big query data transformation logic

Question

I am trying to get a data in a specified format, currently this is in the form.

I want to write a sql query which would get me the data in this form

I tried with converting it to array and unnest but not getting the desired result, any help would be much appreciated. Thanks!

I tried with converting it to array and unnest but not getting the desired result, any help would be much appreciated. Thanks!

Answer 1

As I asked in the comment, it would be better if you can provide more details about the data. However, here, I created a reproducible version of your data in raw_data, and then I unnested x2 to bring the first part of the query. Here, I had to deduplicate so I used a distinct statement.

In the second part, you want to match y and z by their indexes, so I used a where statement in the bottom.

I hope it solves the problem.

with
raw_data as
(
  select 
    3 as event_id, 
    [56238.11, 56238.11] as x,
    [1, 10] as y,
    [41171.62, 102813.99] as z
  union all
  select 
    9 as event_id,
    [1098.13, 1098.13] as x,
    [1, 10] as y,
    [612.72, 1135.72] as z
)
select distinct
  event_id,
  x2
from raw_data
join unnest(x) as x2

union all

select
  y2,
  z2
from raw_data
join unnest(y) as y2 with offset y_i
join unnest(z) as z2 with offset z_i
where y_i = z_i