BigQuery 支持哪些 JsonPath 表达式?

问题描述

我阅读了 BigQuery 文档，它支持 JsonPath 表达式语言的子集.但是我找不到实际支持的 JsonPath 的哪些部分?例如，当我在控制台中尝试时，我似乎无法在 BigQuery 的 JsonPath 表达式中使用通配符或过滤器.

I read in the BigQuery documentation that it supports a subset of the JsonPath expression language. But I cannot find which parts of JsonPath that actually is supported? For example I cannot seem to use wildcards or filters in my JsonPath expressions in BigQuery when I try it out in the console.

1. 是否可以在 BigQuery 的 JsonPath 表达式中使用通配符和过滤器?
2. 是否有参考文档或其他文档描述 BigQuery 中的完整 JsonPath 支持(因为我似乎找不到它)?

1. Is it possible to use wildcards and filters in JsonPath expressions in BigQuery?
2. Is there a reference documentation or other documentation describing the full JsonPath support in BigQuery (because I cannot seem to find it)?

推荐答案

为了克服 BigQuery 对 JsonPath 的限制"，可以引入 自定义函数 如下例所示:
注意:它使用可以从 https://code 下载的 jsonpath-0.8.0.js.google.com/archive/p/jsonpath/downloads 并上传到 Google Cloud Storage - gs://your_bucket/jsonpath-0.8.0.js

To overcome BigQuery "limitation" for JsonPath, one can introduce custom function as below example shows:
Note : it uses jsonpath-0.8.0.js that can be downloaded from https://code.google.com/archive/p/jsonpath/downloads and uploaded to Google Cloud Storage - gs://your_bucket/jsonpath-0.8.0.js

#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 t AS (
SELECT '''
{ "store": {
        "book": [
            { "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
            },
            { "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
            },
            { "category": "fiction",
                "author": "Herman Melville",
                "title": "Moby Dick",
                "isbn": "0-553-21311-3",
                "price": 8.99
            },
            { "category": "fiction",
                "author": "J. R. R. Tolkien",
                "title": "The Lord of the Rings",
                "isbn": "0-395-19395-8",
                "price": 22.99
            }
        ],
        "bicycle": {
            "color": "red",
            "price": 19.95
        }
    }
}
''' AS x
)
SELECT
    CUSTOM_JSON_EXTRACT(x, '$.store.book[*].author'),
    CUSTOM_JSON_EXTRACT(x, '$..*[?(@.price==22.99)].author'),
    CUSTOM_JSON_EXTRACT(x, '$..author'),
    CUSTOM_JSON_EXTRACT(x, '$.store.*'),
    CUSTOM_JSON_EXTRACT(x, '$.store..price'),
    CUSTOM_JSON_EXTRACT(x, '$..book[(@.length-1)]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[-1:]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[0,1]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[:2]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[?(@.isbn)]')
FROM t

结果如下

对于 CUSTOM_JSON_EXTRACT(x, '$.store.book[*].author')

[
  "Nigel Rees"
  "Evelyn Waugh"
  "Herman Melville"
  "J. R. R. Tolkien"
]

对于 CUSTOM_JSON_EXTRACT(x, '$..*[?(@.price==22.99)].author')

[
  "J. R. R. Tolkien"
]

对于 CUSTOM_JSON_EXTRACT(x, '$.store..price')

[
  8.95
  12.99
  8.99
  22.99
  19.95
]

等等......

如您所见 - 现在您可以使用通配符和过滤器以及所有爵士乐 :o)

As you can see - now you can use wildcard and filters and all that jazz :o)

这篇关于BigQuery 支持哪些 JsonPath 表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！