0
Follow
0
View

BigQuery UDF user defined functions return types

a58438331 注册会员
2023-01-25 23:42

Revised Answer

I've given the original ask another read and contrasted with some experimentation in my test data set.

While I'm unable to reproduce the given error, I did experience related difficulty with the following line:

unnest([struct(`dataset.json_extract_all_leaves`(setting_value) as json)]),

Put simply, the function being called takes a string (presumably a stringified JSON value) and returns a similarly stringified JSON value with the result. Because UNNEST can only be used with arrays, the author surrounds the output with [struct and ] which may be the issue. Again, in an effort to yield the same result as I do below, but using the original functions, I would propose that the SQL block be updated to the following:

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));
  ''';
WITH extract_all AS (
  select 
    id, 
    field,
    created,
    properties
    FROM 
      UNNEST([
        STRUCT(1, 'michael', DATE(2022, 5, 1), '[[{"name":"Andy","age":7},{"name":"Mark","age":5},{"name":"Courtney","age":6}], [{"name":"Austin","age":8},{"name":"Erik","age":6},{"name":"Michaela","age":6}]]'),
        STRUCT(2, 'sarah', DATE(2022, 5, 2), '[{"name":"Angela","age":9},{"name":"Ryan","age":7},{"name":"Andrew","age":7}]'),
        STRUCT(3, 'rosy', DATE(2022, 5, 3), '[{"name":"Brynn","age":4},{"name":"Cameron","age":3},{"name":"Rebecca","age":5}]')
      ])
    AS myData
)

SELECT
  id,
  field,
  created,
  key,
  value
FROM (
  SELECT 
    *
  FROM extract_all,
  UNNEST(extract_keys(extract_all_leaves(properties))) key WITH OFFSET
  JOIN UNNEST(extract_values(extract_all_leaves(properties))) value WITH OFFSET
  USING(OFFSET)
)

Put simply - remove the extract_all_leaves line with its array casting and perform it in the offset-joined pair of keys and values, then put all that in a subquery so you can cleanly pull out just the columns you want.

And to explicitly answer the asked question, I believe the issue is in the SQL because of the type casting in the offending line and my own inability to get it to cleanly pair with the subsequent UNNEST queries against its output.

Original Answer

I gather that you've got some sort of JSON object in your settings_value field and you're trying to sift out a result that shows the keys and values of that object alongside the other columns in your dataset.

As others mentioned in the comments, this is a bit of a puzzle to figure out precisely why your query isn't working without any sample data, so happy to re-visit this if you can provide a record or two I can drop in to validate against, but here's an end-to-end that yields my guess as to what you're aiming for. In lieu of that, I've created some sample records intended to be in the same spirit of what you provided.

Based on your use of joining by the offset, I'm supposing that you're really just wanting to see all the keys and their values, paired with the other columns. Assuming that's true, I propose using a different JavaScript function that yields an array of all the key/value pairs instead of two separate functions to yield their own arrays. It simplifies the query (and more importantly, works):

create temp function extract_all_leaves(input string) returns string language js as r'''
  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 temp function extract_key_values(input string) returns array> language js as r"""
    var parsed = JSON.parse(input);
    var keys = Object.keys(parsed);
    var result = [];
    for (var ii = 0; ii < keys.length; ii++) {
        var o = {key: keys[ii], value: parsed[keys[ii]]};
        result.push(o);
    }
    return result;
""";
WITH extract_all AS (
  select 
    id, 
    field,
    created,
    properties
    FROM 
      UNNEST([
        --STRUCT(1, 'michael', DATE(2022, 5, 1), '[[{"name":"Andy","age":7},{"name":"Mark","age":5},{"name":"Courtney","age":6}], [{"name":"Austin","age":8},{"name":"Erik","age":6},{"name":"Michaela","age":6}]]'),
        STRUCT(2, 'sarah', DATE(2022, 5, 2), '[{"name":"Angela","age":9},{"name":"Ryan","age":7},{"name":"Andrew","age":7}]'),
        STRUCT(3, 'rosy', DATE(2022, 5, 3), '[{"name":"Brynn","age":4},{"name":"Cameron","age":3},{"name":"Rebecca","age":5}]')
      ])
    AS myData
)

SELECT 
  id,
  field,
  created,
  key,
  value
FROM (
  SELECT 
    *
  FROM extract_all
  CROSS JOIN UNNEST(extract_key_values(extract_all_leaves(properties)))
)

And I believe this yields a result more like what you're seeking:

id field created key value
2 sarah 2022-05-02 0.name Angela
2 sarah 2022-05-02 0.age 9
2 sarah 2022-05-02 1.name Ryan
2 sarah 2022-05-02 1.age 7
2 sarah 2022-05-02 2.name Andrew
2 sarah 2022-05-02 2.age 7
3 rosy 2022-05-03 0.name Brynn
3 rosy 2022-05-03 0.age 4
3 rosy 2022-05-03 1.name Cameron
3 rosy 2022-05-03 1.age 3
3 rosy 2022-05-03 2.name Rebecca
3 rosy 2022-05-03 2.age 5

Of course, if this isn't at all in the right place of where you're trying to get to.

About the Author

Question Info

Publish Time
2023-01-25 23:42
Update Time
2023-01-25 23:42

Related Question

项目启动报错 localStorage is not defined

中级docker构建容器在USER根命令下失败

按current_user求和所有事务

在AWS Cloudfront上,我想将/api/*请求路由到一个外部api

Cloudflare Workers上的“图像未定义”错误

main.c:(.text+0x7b): undefined reference to

Adaboost中有错误name 'y_train' is not defined

Windows11移动user文件夹后,该文件夹消失

Laravel Mix: npm ERR!解析:undefined@undefined,发现:webpack@5.60.0

cp:不能创建普通文件:runAsUser 1337的权限被拒绝