bigquery中的jsonpath不支持@作为过滤器.对替代品有何建议? [英] jsonpath in bigquery doesn't support @ for filter. Suggestions for alternatives?

问题描述

在 https://jsonpath.curiousconcept.com/中，带有以下JSON文档:

At https://jsonpath.curiousconcept.com/ with this JSON document:

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

我可以使用JsonPath表达式 $ [?(@.key =="age")].value 提取值 ["8"] 我想要.

I can use JsonPath expression $[?(@.key=="age")].value to extract the value ["8"] which is what I want.

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

However, when I try and use that same JsonPath in bigquery like so:

select JSON_EXTRACT_SCALAR('[{"key":"name"，"value":"john-doe"}，{"key":"age"，"value":"8"}，{"key":性别"，"value":男性"}]'，'$ [?(@.key =="age")].value')

我收到错误

JSONPath中不受支持的运算符:@

Unsupported operator in JSONPath: @

关于在bigquery中如何实现此目标的任何建议?

Any suggestions as to how to achieve this in bigquery?

推荐答案

不幸的是不得不使用正则表达式:(

Had to resort to regex unfortunately :(

select regexp_extract('[{"key":"name"，"value":"john-doe"}，{"key":"age"，"value":"8"}，{"key":性别"，"value":男性"}]'，r'"key":年龄"，"value":([[^ ,:] +)"')

我之所以说不幸"，是因为我认为JsonPath将是实现这一目标的一种更为优雅的方式.

I say "unfortunately" because I'm of the opinion that JsonPath would be a more elegant way of achieving this.

这篇关于bigquery中的jsonpath不支持@作为过滤器.对替代品有何建议?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！