jsonpath in bigquery doesn't support @ for filter. Suggestions for alternatives?

Question

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

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

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

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": "gender","value": "male"}]', '$[?(@.key=="age")].value')

I get an error

Unsupported operator in JSONPath: @

Any suggestions as to how to achieve this in bigquery?

Answer 1

Had to resort to regex unfortunately :(

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

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