google-bigquery - bigquery 中的 jsonpath 不支持 @ 过滤器。替代方案的建议？

Question

在 https://jsonpath.curiousconcept.com/使用此 JSON 文档:

[{
    "key": "name",
    "value": "john doe"
}, {
    "key": "age",
    "value": "8"
}, {
    "key": "gender",
    "value": "male"
}]

我可以使用 JsonPath 表达式 $[?(@.key=="age")].value 来提取值 ["8"] 这是什么我要。

但是，当我尝试像这样在 bigquery 中使用相同的 JsonPath 时:

选择 JSON_EXTRACT_SCALAR('[{"key": "name","value": "john-doe"}, {"key": "age","value": "8"}, { "key": "gender","value": "male"}]', '$[?(@.key=="age")].value')

我得到一个错误

Unsupported operator in JSONPath: @

关于如何在 bigquery 中实现这一点有什么建议吗？

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS STRING
LANGUAGE js AS """
    try { var parsed = JSON.parse(json);
        return JSON.stringify(jsonPath(parsed, json_path));
    } catch (e) { return null }
"""
OPTIONS (
    library="https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jsonpath/jsonpath-0.8.0.js.txt"
);
WITH `project.dataset.your_table` AS (
  SELECT '''
    [{
        "key": "name",
        "value": "john doe"
    }, {
        "key": "age",
        "value": "8"
    }, {
        "key": "gender",
        "value": "male"
    }]  
      ''' str
)
SELECT CUSTOM_JSON_EXTRACT(str, '$[?(@.key=="age")].value')
FROM `project.dataset.your_table`  

注意:可以从 https://code.google.com/archive/p/jsonpath/downloads 直接链接到托管的 jsonpath-0.8.0.js 库.

如果您愿意，可以将文件复制到 Cloud Storage 上的 your_bucket，并使用“gs://your_bucket/path/jsonpath-0.8.0.js”作为 OPTIONS 库 URI。

上述方法克服了 BigQuery 对 JsonPath 的“限制”，现在您可以使用所有“常规”JsonPath 功能