google-bigquery - 用于扩展 json 以记录的 BigQuery UDF

Question

我有一个像这样的（JSON）字符串列：

[{"someValue":6,"other":true, "maybeThisValue":"exists?"},{"someValue": "cool value", "other": null}]

我想结束这个：

+------------+-------+----------------+
| someValue  | other | maybeThisValue |
+------------+-------+----------------+
| 6          | true  | exists?        |
+------------+-------+----------------+
| cool value |       |                |
+------------+-------+----------------+

因为架构变化很大，所以我想获取字符串的键并从中动态生成记录（没有数组或结构）。输入 JSON 将始终是具有单一深度键值（无子节点）的对象数组。

我在想也许这对于一个 UDF 是不可能的，因为我们必须提前指定返回值，并且可能会有额外的列。但也许这可以通过两个 UDF 实现？一个 UDF 可以生成并保存然后执行另一个 UDF 吗？

或者，也许我可以从一个 UDF 开始，它会给我（JSON）字符串的唯一键，然后编写一个 dbt 宏，它将使用该函数调用的返回？（我不确定这是可能的）

我知道如何在postgres中执行此操作，但我想知道您是否有任何想法让它在 BigQuery 中工作，或者是否可以在 UDF 中完成，即使它有点慢也会非常酷

转换数据类型很痛苦，但如果它可以检测到前 5 列都是数字，并且将 safe_cast 转换为 int，那将真的很酷......我不知道该怎么做，所以 string 很好

与此类似：

https://dba.stackexchange.com/questions/151838/postgresql-json-column-to-view

https://github.com/fishtown-analytics/dbt-utils/blob/master/macros/sql/get_column_values.sql

也许自定义 JSONPath 是可能的'*.*'：BigQuery 支持哪些 JsonPath 表达式？

也许没有array<struct>：/

也许这可以通过 BigQuery 脚本实现？https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting

也许我可以将其用作其中的一部分如何使用 BigQuery 提取 JSON 对象中的所有键

我可以得到这样的值......

CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
"""; 

CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
        return jsonPath(JSON.parse(json), json_path);
"""
OPTIONS (
    library="gs://your_bucket/jsonpath-0.8.0.js"
);

select SPLIT(CUSTOM_JSON_EXTRACT(jsoncolumn,'*.'))
FROM `db.master`,
unnest(json2arrayUDF(JSON_EXTRACT(jsoncolumn, "$"))) jsoncolumn

看起来像：

|1 | 6 |
|  | true |
|  | exists? |
|2 | cool value |
|  |  |
|  |  |

现在我只需要拿到钥匙，然后以某种方式匹配这两件事......

标签： google-bigqueryuser-defined-functions