Как JSON_EXTRACT использовать ключ из столбца?

Question

Функция Bigquery JSON_EXTRACT принимает строку в качестве ключа для возврата значения. Можно ли взять строку из столбца?

json_string_expr обрабатывать это как {'A': '123', 'B': '456'}, Я хочу взять ключ из столбца list, Таблица Bigquery и ожидаемый результат приведены ниже.

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

JSON_EXTRACT(json_string_expr, '$.list') не может дать ожидаемый результат, потому что функция ожидает строку после $., Любая идея, как вернуть ожидаемый результат, используя стандартный SQL?

Answer 1

Ниже для BigQuery Standard SQL

К сожалению, JSONPath должен быть строковым литералом или параметром запроса, поэтому см. Обходной путь ниже

#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`  

Другая, менее подробная версия

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

если применить обе приведенные выше версии к образцу данных из вашего вопроса - результат

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