0
170
ViewsHow to return a MAP like structure from bigquery javascript UDF so that I can generate a key-value column dynamically at runtime?
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 | |
|---|---|
| ram | ram@gmail.com |
1 answers
Answer question0
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
