BigQuery中将字典格式列拆分为两列多行的实现方法

我有一张表，其中一列存储字典格式数据，结构如下：

product   |  dict_discounts 
   A      | {"May":0.03, "June":0.1, "July":0.001}
   B      | {"May":0.002, "June":0.003, "July":0.01, "August":0.2, "September":0.5}

希望将其转换为如下格式：

product   |  month       | discount
   A      |  May         | 0.03
   A      |  June        | 0.1
   A      |  July        | 0.001
   B      |  May         | 0.002
   B      |  June        | 0.003
   B      |  July        | 0.01
   B      |  August      | 0.2
   B      |  September   | 0.5

我尝试用SPLIT函数，但操作繁琐，当前代码如下：

SELECT 
     product,
     dict_discounts_values

FROM table a,
UNNEST(SPLIT(a.feature_value, ',')) AS dict_discounts_values

得到的结果不符合预期：

product   |  dict_discounts_values     
   A      |  {"June":0.1      
   A      |  "July":0.001   
   .
   .
   .

请问如何正确实现需求？

直接用字符串拆分容易出错，应该利用数据库的JSON解析函数来处理这类结构化数据，以下是主流SQL引擎的实现方式：

1. BigQuery #

BigQuery支持直接解析JSON对象并展开键值对，有两种简洁实现方式：

-- 方式1：通过JSON_KEYS提取所有月份，再匹配对应折扣
SELECT
  product,
  month,
  CAST(JSON_EXTRACT_SCALAR(dict_discounts, CONCAT('$.', month)) AS FLOAT64) AS discount
FROM
  `your_table`,
  UNNEST(JSON_KEYS(dict_discounts)) AS month

-- 方式2：直接展开JSON键值对
SELECT
  product,
  JSON_EXTRACT_SCALAR(kv, '$.key') AS month,
  JSON_EXTRACT_SCALAR(kv, '$.value') AS discount
FROM
  `your_table`,
  UNNEST(JSON_EXTRACT_ARRAY(JSON_QUERY(dict_discounts, '$'))) AS kv

2. PostgreSQL #

PostgreSQL的jsonb/json类型可以用jsonb_each（或json_each）直接展开键值对：

SELECT
  product,
  kv.month,
  kv.discount::FLOAT
FROM
  your_table,
  jsonb_each(dict_discounts::jsonb) AS kv(month, discount)

如果列是json类型，将jsonb_each替换为json_each即可。

3. MySQL 8.0+ #

MySQL 8.0及以上版本可以用JSON_TABLE解析并展开JSON对象：

SELECT
  t.product,
  j.month,
  JSON_EXTRACT(t.dict_discounts, CONCAT('$.', j.month)) AS discount
FROM
  your_table t,
  JSON_TABLE(
    JSON_KEYS(t.dict_discounts),
    '$[*]' COLUMNS(month VARCHAR(20) PATH '$')
  ) j

4. SQL Server #

SQL Server使用OPENJSON来解析JSON对象并展开：

SELECT
  t.product,
  j.month,
  j.discount
FROM
  your_table t
CROSS APPLY
  OPENJSON(t.dict_discounts)
  WITH (
    month VARCHAR(20) '$',
    discount FLOAT '$.value'
  ) j

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