bigquery中的jsonpath不支持@for filter。关于替代方案的建议?

下面是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="gs://your_bucket/jsonpath-0.8.0.js"
);
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`  

注意:您需要将jsonpath-0.8.0.js库上传到云存储上的存储桶中。它可以从 https://code.google.com/archive/p/jsonpath/downloads .

上面的方法克服了JsonPath的BigQuery“限制”,现在可以使用所有“常规”JsonPath特性

不幸的是不得不求助于regex:(

select regexp_extract('[{"key": "name","value": "john-doe"}, {"key": "age","value": "8"}, {"key": "gender","value": "male"}]', r'"key": "age","value": "([^,:]+)"')

我之所以说“不幸”,是因为我认为JsonPath将是实现这一目标的更优雅的方法。