What JsonPath expressions are supported in BigQuery?

Question

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.

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

score 13 · Accepted Answer · edited Dec 30 '18 at 12:34

Is it possible to use wildcards and filters in JsonPath expressions in BigQuery?

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

Result is as below

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

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

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

[
  "J. R. R. Tolkien"
]

For CUSTOM_JSON_EXTRACT(x, '$.store..price')

and so on ...

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

score 3 · Answer 2 · answered Oct 14 '17 at 15:05

3

The supported elements are in the table of the section that you linked to. Specifically, it includes $, ., and [], where the latter can be either a child operator or a subscript (array) operator. If something is not listed, it is not supported.

answered Oct 14 '17 at 15:05

Elliott Brossard

32,095
2
67
99

What JsonPath expressions are supported in BigQuery?

2 Answers2

Linked