How to return a MAP like structure from bigquery javascript UDF so that I can generate a key-value column dynamically at runtime?

Question

I'm trying to return a MAP like structure from Javascript UDF in bigquery. So that I can convert that structure directly into relational columns without knowing the column named prior. In below approach, I'm trying to stringify JSON and then using json_extract_scaler function to create columns.

CREATE TEMP FUNCTION main(json_str STRING)
RETURNS STRING
LANGUAGE js AS 
r"""
var row = JSON.parse(json_str);
return JSON.stringify(row);
""";

with temp_table as (
  select "ram" name, "ram@gmail.com" email
),
Rule_result as (SELECT main(TO_JSON_STRING(STRUCT(t.name, t.email))) result FROM temp_table as t)
SELECT json_extract_scalar(result, '$.name') name,  
json_extract_scalar(result, '$.email') email
FROM Rule_result as r;

In this approach, I'm returning the struct, knowing the column names beforehand.

CREATE TEMP FUNCTION main(json_str STRING)
RETURNS STRUCT<name STRING, email STRING>
LANGUAGE js AS 
r"""
var row = JSON.parse(json_str);
return row;
""";

with temp_table as (
  select "ram" name, "ram@gmail.com" email
),
Rule_result as (SELECT main(TO_JSON_STRING(STRUCT(t.name, t.email))) result FROM temp_table as t)
SELECT r.result.* FROM Rule_result as r;

Both the approaches work fine. But it doesn't solve the problem. Because I need to be aware of the column names. Bigquery supports struct return type but that doesn't fit my usecase. Since I'm not aware about the column names beforehand. How can I dynamically create columns from the data return by the javascript UDF without knowing the column names?

• data is flat JSON object

{
  "name":"ram",
  "email":"ram@gmail.com"
}

I somehow need to convert this JSON object into table columns like

name	email
ram	ram@gmail.com

Answer 1

Consider below

create temp function  extract_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
""";
create temp function  extract_values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
""";
create temp table tmp as 
select id, key, value 
from your_table,
unnest(extract_keys(json)) key with offset
join unnest(extract_values(json)) value with offset
using(offset);

execute immediate (select
'''select * from tmp
pivot (any_value(value) for key in (''' || string_agg(distinct "'" || key || "'") || '''))
'''
from tmp
);    

if applied to sample data like in your question's example

output is