I am using the following SQL (from another question) which contains temporary functions.
create temp function extract_keys(input string) returns array language js as """
return Object.keys(JSON.parse(input));
""";
create temp function extract_values(input string) returns array language js as """
return Object.values(JSON.parse(input));
""";
create temp function extract_all_leaves(input string) returns string language js as '''
function flattenObj(obj, parent = '', res = {}){
for(let key in obj){
let propName = parent ? parent + '.' + key : key;
if(typeof obj[key] == 'object'){
flattenObj(obj[key], propName, res);
} else {
res[propName] = obj[key];
}
}
return JSON.stringify(res);
}
return flattenObj(JSON.parse(input));
''';
select col || replace(replace(key, 'value', ''), '.', '-') as col, value,
from your_table,
unnest([struct(extract_all_leaves(data) as json)]),
unnest(extract_keys(json)) key with offset
join unnest(extract_values(json)) value with offset
using(offset)
I want to save the above query as a view, but I cannot include the temporary functions, so I planned to define these as user-defined functions that can be called as part of the view.
When defining the functions, I'm having some trouble getting the input and output types defined correctly. Here's the three user defined functions.
CREATE OR REPLACE FUNCTION `dataset.json_extract_all_leaves`(Obj String)
RETURNS String
LANGUAGE js AS """
function flattenObj(obj, parent = '', res = {}){
for(let key in obj){
let propName = parent ? parent + '.' + key : key;
if(typeof obj[key] == 'object'){
flattenObj(obj[key], propName, res);
} else {
res[propName] = obj[key];
}
}
return JSON.stringify(res);
}
return flattenObj(JSON.parse(input));
"""
CREATE OR REPLACE FUNCTION `dataset.json_extract_keys`(input String)
RETURNS Array<String>
LANGUAGE js AS """
return Object.keys(JSON.parse(input));
"""
finally
CREATE OR REPLACE FUNCTION `dataform.json_extract_values`(input STRING)
RETURNS Array<String>
LANGUAGE js AS """
return Object.values(JSON.parse(input));
"""
Those three functions are created successfully, but when I come to use them in this view
WITH extract_all AS (
select
id,
field,
created,
key || replace(replace(key, 'value', ''), '.', '-') as key_name, value,
FROM `dataset.raw_keys_and_values`,
unnest([struct(`dataset.json_extract_all_leaves`(setting_value) as json)]),
unnest(`dataset.json_extract_keys`(json)) key with offset
join unnest(`dataset.json_extract_values`(json)) value with offset
using(offset)
)
SELECT *
FROM
extract_all
This fails with the following error
Error: Multiple errors occurred during the request. Please see the `errors` array for complete details. 1. Failed to coerce output value "{\"value\":true}" to type ARRAY<STRING>
I understand there's a mismatch somewhere between the expected return value of json_extract_values, but I can't understand if it's in the SQL or JavaScript UDF?
