Extract nested values as columns Google BigQuery?-CodePudding

I have a table with nested values, like the following:

I'd like to grab the values, with keys as columns without multiple cross joins. i.e.

SELECT 
owner_id, 
owner_type, 
domain, 
metafields.value AS name, 
metafields.value AS image, 
metafields.value AS location, 
metafields.value AS draw
FROM 
example_table

Obviously, the above won't work for this, but the following output would be desired:

In the actual table there are hundreds of metafields per owner_id, and hundreds of owner_ids, and owner_types. Multiple joins to other tables for owner_types is fine, but for the same owner type, I don't want to have to join multiple times.

Basically, I need to be able to select the key to which the column corresponds, and display the relevant value for that column. Without, having to display every metafield available.

Any way of doing this?

CodePudding user response：

You can use the subqueries and SAFE_offset statement. With the information you share, you can use the query below.

WITH sequences AS
 (
     SELECT 1 as ID,"product" AS owner_type,"beta.com" AS domain,["name","image","lcation","draw"] AS metalfields_key, ["big","pic.png","utha","1"] AS metalfields_value
     ),
 Val as(
 SELECT distinct id, owner_type,domain, value FROM sequences, sequences.metalfields_value as value, sequences.metalfields_key
 ), text as(
 SELECT
 id, owner_type, domain,
 STRING_AGG(value ORDER BY value) AS Text
FROM Val 
GROUP BY owner_type, domain, id
 )
 
SELECT DISTINCT t1.id, t1.owner_type,domain,
split(t1.text, ',')[SAFE_offset(1)] as name,
split(t1.text, ',')[SAFE_offset(2)] as image,
split(t1.text, ',')[SAFE_offset(3)] as location,
split(t1.text, ',')[SAFE_offset(0)] as draw
from text as t1

You can see the result.

CodePudding user response：

Consider below approach

select * except(id) from (
  select t.* except(metafields), 
    to_json_string(t) id, key, value
  from your_table t, unnest(metafields) kv
)
pivot (min(value) for key in ('name', 'image', 'location', 'draw'))

if applied to sample data in your question - output is