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?

provide more examples of `json_string_expr` and `list` and expected output — Mikhail Berlyant, Jun 22 '19 at 15:49
oh, ok so list is actually not list(array) but rather string with just one item to be extracted — Mikhail Berlyant, Jun 22 '19 at 16:16

Mikhail Berlyant · Accepted Answer · 2019-06-22T16:49:17.550

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

thanks, I tried the less verbose version. It works! – Layla Jun 23 '19 at 04:14 — Layla, Jun 23 '19 at 04:14

how to JSON_EXTRACT using key from a column?

1 Answers1