BigQuery中JSON_EXTRACT无法提取带转义引号的lvl1_id键值

问题原因 #

你的same_day_value字段并非嵌套JSON对象，而是一个包含转义JSON的字符串。直接对该字符串使用JSON_EXTRACT无法识别其中的JSON结构，导致返回NULL。

修正后的SQL语句 #

SELECT 
  JSON_EXTRACT(value, '$.Data') AS data_obj,
  JSON_EXTRACT_SCALAR(value, '$.Data.same_day_value') AS same_day_value_str,
  JSON_EXTRACT(JSON_EXTRACT_SCALAR(value, '$.Data.same_day_value'), '$.lvl1_id') AS lvl1_id,
  -- 若需要不带引号的纯值，使用JSON_EXTRACT_SCALAR
  JSON_EXTRACT_SCALAR(JSON_EXTRACT_SCALAR(value, '$.Data.same_day_value'), '$.lvl1_id') AS lvl1_id_raw
FROM `tbl`

步骤说明 #

1. JSON_EXTRACT_SCALAR(value, '$.Data.same_day_value')：提取same_day_value的原始字符串内容（去除外层引号，保留内部转义结构）。
2. 将提取出的字符串作为JSON输入：对该字符串再次使用JSON_EXTRACT或JSON_EXTRACT_SCALAR，即可正确解析其中的lvl1_id字段。

内容的提问来源于stack exchange，提问作者Drake