how to JSON_EXTRACT using key from a column?

Question

Bigquery JSON_EXTRACT function takes a string as the key to return the value. Is it possible to take the string from a column?

The json_string_expr to process is like {'A': '123', 'B': '456'}. I want to take the key from a column list. The Bigquery table and expected result are as below.

   |   json_string_expr        | list   | expected_result
   |{'A': '123', 'B': '456'}   | A      |  '123'
   |{'A': '789', 'B': '012'}   | B      |  '012'

JSON_EXTRACT(json_string_expr, '$.list') couldn't give the expected result, because the function expects a string after $.. Any idea how to return the expected result using standard SQL?

Answer 1

Below is for BigQuery Standard SQL

Unfortunately, JSONPath must be a string literal or query parameter, so see workaround below

#standardSQL
SELECT json_string_expr, list,
  (SELECT SPLIT(kv, ':')[SAFE_OFFSET(1)]
    FROM UNNEST(SPLIT(REGEXP_REPLACE(json_string_expr, r"[{} ']", ''))) kv 
    WHERE SPLIT(kv, ':')[SAFE_OFFSET(0)] = list
  ) value
FROM `project.dataset.table`  

Another, less verbose version is

#standardSQL
SELECT json_string_expr, list,
  REGEXP_EXTRACT(json_string_expr, CONCAT(r"'", list, "': '(.*?)'")) value
FROM `project.dataset.table`

if to apply both above versions to sample data from your question - result is

Row json_string_expr            list    value    
1   {'A': '123', 'B': '456'}    A       123  
2   {'A': '789', 'B': '012'}    B       012