json - 如何从在 BigQuery 中没有键的 JSON 中读取数据？

Question

我有这种格式的数据

{"OgDKrh1hT6NzBHXsjh7n8yrkrE62":["U2","X1","D1","C3","H2","D3","O3","Q1","A2"],"UxdTolmzq9cAYjPkTX57VAzas2w1":["E3","S1","V2","W1","M1","H3","L3","G2","Y3"]}

我想要这种格式的数据

id                              details
OgDKrh1hT6NzBHXsjh7n8yrkrE62    U2,X1,D1,C3,H2,D3,O3,Q1,A2
UxdTolmzq9cAYjPkTX57VAzas2w1    E3,S1,V2,W1,M1,H3,L3,G2,Y3

标签： jsongoogle-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
select 
  arr[offset(0)] as id,
  arr[offset(1)] as details
from `project.dataset.table`,
unnest(regexp_extract_all(text, r'"[^"]+":\[[^\[]+\]')) kv,
unnest([struct(split(translate(kv, '"[]', ''), ':') as arr)])    

如果将上述应用到您的问题中的样本数据，如下例所示

#standardSQL
with `project.dataset.table` as (
  select '{"OgDKrh1hT6NzBHXsjh7n8yrkrE62":["U2","X1","D1","C3","H2","D3","O3","Q1","A2"],"UxdTolmzq9cAYjPkTX57VAzas2w1":["E3","S1","V2","W1","M1","H3","L3","G2","Y3"]}' text
)
select 
  arr[offset(0)] as id,
  arr[offset(1)] as details
from `project.dataset.table`,
unnest(regexp_extract_all(text, r'"[^"]+":\[[^\[]+\]')) kv,
unnest([struct(split(translate(kv, '"[]', ''), ':') as arr)])    

输出是