Извлечение ключей и значений из строки json в bigquery, где в документе json нет указанного ключа

Question

У меня есть таблица в BigQuery, где у меня есть объект, и для каждого объекта у меня есть несколько строковых JSON. В json пример строки выглядит следующим образом:

{
    "ObjectID": "1984931229",
    "indexed_abstract": "{\"IndexLength\":123,\"InvertedIndex\":{\"Twenty-seven\":[0],\"metastatic\":[1,45],\"breast\":[2],\"adenocarcinoma\":[3],\"patients,\":[4]}}" 
}

где внутри indexed_abstract у нас есть InvertedIndex который содержит некоторые ключевые слова и сколько раз эти ключевые слова появились в ObjectID,

Теперь я хочу получить доступ к строковому JSON, анализируя JSON с помощью BigQuery и для каждого ObjectID Я хочу создать вложенное поле, в котором у меня есть ключевое слово, соответствующий массив и длина соответствующего массива.

Например, в этом случае вывод будет выглядеть следующим образом:

+------------+----------------+---------------+-------------------+
|  ObjectID  |  keyword.key   | keyword.count | keyword.positions |
+------------+----------------+---------------+-------------------+
| 1984931229 | Twenty-seven   |             1 | [0]               |
|            | metastatic     |             2 | [1,45]            |
|            | breast         |             1 | [2]               |
|            | adenocarcinoma |             1 | [3]               |
|            | patients       |             1 | [4]               |
+------------+----------------+---------------+-------------------+

Я понимаю, что мог бы использовать функцию JSON_EXTRACT, но я не уверен, каким будет мой ключ внутри инвертированного индекса для доступа к ключевым словам и массивам, соответствующим им.

Answer 1

Ниже для BigQuery Standard SQL

#standardSQL
SELECT ObjectID, 
  ARRAY(
    SELECT AS STRUCT 
      key, 
      ARRAY_LENGTH(SPLIT(value)) `count`, 
      value positions 
    FROM UNNEST(REGEXP_EXTRACT_ALL(JSON_EXTRACT(indexed_abstract, '$.InvertedIndex'), r'"[^"]+":\[[\d,]*?]')) pair,
    UNNEST([STRUCT(REPLACE(SPLIT(pair, ':')[OFFSET(0)], '"', '') AS key, SPLIT(pair, ':')[OFFSET(1)] AS value)])
  ) keyword
FROM `project.dataset.table`

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

Row ObjectID    keyword.key     keyword.count   keyword.positions    
1   1984931229  Twenty-seven    1               [0]  
                metastatic      2               [1,45]   
                breast          1               [2]  
                adenocarcinoma  1               [3]  
                patients        1               [4]  

Обновление комментария Оп - мне было интересно, если бы я хотел сделать позиции массивом (повторяющимся полем), как бы я это сделал?

Изменение должно быть сделано в одну строку

  SPLIT(REGEXP_REPLACE(value, r'\[|]', '')) positions