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

Below is for BigQuery Standard 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`  

Note: it is possible to link directly to the hosted jsonpath-0.8.0.js library from https://code.google.com/archive/p/jsonpath/downloads.

If you prefer, you may copy the file to your_bucket on Cloud Storage and use "gs://your_bucket/path/jsonpath-0.8.0.js" for the OPTIONS library URI.

Above approach overcomes BigQuery "limitation" for JsonPath and now you can use all 'regular' JsonPath features