Error when trying to have a variable pathsname: JSONPath must be a string literal or query parameter

Question

I'm trying to use JSON_EXTRACT in Bigquery with a JSONPATH that is not always the same.

So my L.key is always a different keyword (which I have in table2). Unfortunately, concat with JSON_EXTRACT doesn't work for me.
If I use concat alone, without JSON_EXTRACT, it works.

This is the code I'm using:

SELECT A.*, SAFE_CAST(REPLACE(JSON_EXTRACT(A.some_json_obj, concat("$.", L.key)), '\"', '') AS NUMERIC) AS obp 
FROM table1 A, table2 L 
WHERE A.name = L.name

Answer 1

Below is for BigQuery Standard SQL

To work around BigQuery's 'limitation' for JsonPath, you can use custom function as in below example:

#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) { returnnull }
"""
OPTIONS (
    library="gs://your_bucket/jsonpath-0.8.0.js"
);
SELECT A.*, 
  SAFE_CAST(REGEXP_REPLACE(CUSTOM_JSON_EXTRACT(A.some_json_obj, CONCAT("$.", L.key)), r'["\[\]]', '') AS NUMERIC) AS obp 
FROM table1 A, table2 L 
WHERE A.name = L.name

Note : you need download jsonpath-0.8.0.js from https://code.google.com/archive/p/jsonpath/downloads and upload it to your Google Cloud Storage bucket - in this example it is denoted as gs://your_bucket/jsonpath-0.8.0.js

For example, if to apply to below simplified dummy data

WITH table1 AS (
  SELECT 1 name, '{"x":1, "y":"2"}' some_json_obj
), table2 AS (
  SELECT 1 name, 'x' key UNION ALL
  SELECT 1, 'y'
)

result will be

Row name    some_json_obj       obp  
1   1       {"x":1, "y":"2"}    1    
2   1       {"x":1, "y":"2"}    2