sql - 使用 split 或 regex 解析 bigquery 中的数据

Question

我在 platforminfo 列中有这种数据

[{'df'：{}，'badKs'：['[phone]']，'objectId'：'JoF3NUHVYxDAoirH4e6i3g'，'平台'：'Web'}，{'df'：{}，'badKs'：[ '[phone]']，'objectId'：'-vfa995e3b5d384b7dbfd8919bad17c28a'，'平台'：'iOS'，'os_version'：'14.1'，'app_version'：'3.16.2'，'make'：'Apple'，'型号': 'iPhone12,3'}]

[{'df': {}, 'objectId': 'elQ8JEW2klEQFMZ0vXZDhw', 'platform': 'Web', 'phone': 5370002731}, {'df': {}, 'objectId': '__da533f19d1e040bea2b2b58fa6d2ecad', 'platform '：'Android'，'手机'：5370002731，'os_version'：'10'，'app_version'：'3.6.4'，'make'：'三星'，'型号'：'SM-A605G'}]

我想要的是将此数据解析为许多列，但实际上定义相同但命名不同 badKs=phone

我的期望是这样的：

phone         platform  os_version  make 
[phone]  iOS       14.1        Apple
5370002731    Android   10          Samsung

我已经用这个查询做了

split(REGEXP_REPLACE(platforminfo, r'([\{\}\]\'\"])', ''), 'phone:')[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(platforminfo, ']')) - 1)] end

但是，此查询不足以解析数据，如果您能在这种情况下帮助我，将会很有帮助。谢谢

标签： sqlregexsplitgoogle-bigquery

Answer 1

使用以下方法

select 
  translate(coalesce(json_extract(el[offset(0)], '$.badKs'), json_extract(el[offset(0)], '$.phone')), "[]\"", "") as phone,
  json_extract_scalar(el[offset(1)], '$.platform') as platform,
  json_extract_scalar(el[offset(1)], '$.os_version') as os_version,
  json_extract_scalar(el[offset(1)], '$.make') as make
from `project.dataset.table`, unnest([struct(json_extract_array(platforminfo, '$') as el)])    

如果应用于您问题中的样本数据 - 输出是