在BigQuery中从字符串提取用户日程的日期与时段

直接利用BigQuery的JSON处理函数解析多组日程数据，替代仅能提取单组数据的正则表达式方式，具体SQL如下：

SELECT
  name,
  days_str AS Days,
  JSON_EXTRACT_SCALAR(time_slot, '$[0]') AS `Start Time`,
  JSON_EXTRACT_SCALAR(time_slot, '$[1]') AS `End Time`
FROM `projectid.dataset.table`,
-- 拆分JSON对象中的所有日期组键
UNNEST(JSON_OBJECT_KEYS(periods)) AS days_str,
-- 根据日期组键提取对应时间段数组并展开
UNNEST(JSON_EXTRACT_ARRAY(periods, CONCAT('$.', days_str))) AS time_slot
WHERE is_active_cache = 1
LIMIT 100

逻辑说明 #

1. UNNEST(JSON_OBJECT_KEYS(periods))：将原JSON字符串中的所有日期组（如示例2中的"0,1,2,3,6"和"4,5"）拆分为独立行，实现多组日程的拆分。
2. JSON_EXTRACT_ARRAY(periods, CONCAT('$.', days_str))：根据每个日期组键，提取对应的时间段数组（如[["11:00","21:30"]]），再通过UNNEST展开为单个时间段数组元素["11:00","21:30"]。
3. JSON_EXTRACT_SCALAR：从单个时间段数组中分别提取第0位（开始时间）和第1位（结束时间）的字符串值。

输出效果 #

针对示例2的输入，会生成两行结果：

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