Transform flat table into nested/repeated table

Question

in the BigQuery we have a dictionary tables which specify some mappings - example:

But this structure (flat, typically relational one) is not so handy to operate with mappings - when we write a query multiple joins are required if we would like to get mappings for a few fields. Our idea is to transform dictionary table into one-row nested table with repeated fields, to enable mappings application with a single join. So desired structure looks like:

Any idea how to transform the flat table to nested one via standard sql? Essentially, values from field attribute should become a new attributes, and key value value pairs should became a repeated entries for each attribute. So the whole operation is similar to the pivot.

P.S. I know, that BigQuery recently introduced a json structures. We considered this solution, but JSON_QUERY doesn't support passing concatenated values to function parameters. As a result we are unable to get values dynamically, and we resign from this solution as more complicated. Error when trying to have a variable pathsname: JSONPath must be a string literal or query parameter

user_8229534 · Answer

You can use pivot operator along with array_agg() function to achieve this.

Here is a query that does this -

with temp as 
(
    select 'aaa' as field, 1 key, 2 value union all
    select 'aaa' as field, 2 key, 5 value union all
    select 'aaa' as field, 4 key, 15 value union all
    select 'bbb' as field, 1 key, 23 value union all
    select 'bbb' as field, 2 key, 36 value
)

select * 
from 
(
select field, key, value
from temp
)
pivot
(
    array_agg(struct(key, value) ignore nulls) 
    for field in ('aaa','bbb')
)

In the pivot operator, as you can see, I have mentioned the field values as aaa,bbb. If you want to load them dynamically, then you will need to set a variable and then do it.

For more details on storing the values in variable and then using pivot, use this link - https://towardsdatascience.com/pivot-in-bigquery-4eefde28b3be

user_5221944 · Answer

Consider below simple option

select * from your_table pivot (
  array_agg(struct(key, value) ignore nulls) 
  for field in ('aaa','bbb')
)

if applied to sample data in your question - output is

Transform flat table into nested/repeated table

2 Answers