Python或SQL如何实现字典map字段split/explode拆分为多行数据

需求说明 #

原始数据集结构如下：

• 包含ID、country两个字段
• country字段为Map（字典）类型，键为国家编码，值固定为1，原始示例数据：

  ID	country
  01	{"23":1,"45":1,"65":1}
  02	{"23":1,"48":1}
  03	{"65":1}

• 处理目标：将country字典中的每个国家编码键拆分为独立行，ID对应重复匹配，最终每行只对应一个国家编码，拆分后结果示例：

  ID	country
  01	23
  01	45
  01	65
  02	23
  02	48
  03	65

Python实现 #

基于pandas的批量处理方案 #

日常结构化数据处理优先用pandas，核心逻辑是先提取字典所有键生成列表，再调用内置的explode方法完成拆行：

import pandas as pd
import json

# 构造原始数据集
df = pd.DataFrame({
    "ID": ["01", "02", "03"],
    "country": [
        {"23":1,"45":1,"65":1},
        {"23":1,"48":1},
        {"65":1}
    ]
})

# 如果country字段是JSON字符串格式而非原生字典，先执行下面这行转换
# df["country"] = df["country"].apply(json.loads)

# 提取字典键为列表，执行拆行
df["country"] = df["country"].apply(lambda x: list(x.keys()))
result = df.explode("country", ignore_index=True)
print(result)

原生Python实现 #

如果不依赖第三方库，直接遍历原始数据逐行生成结果即可：

# 原始输入数据
raw_data = [
    {"ID": "01", "country": {"23":1,"45":1,"65":1}},
    {"ID": "02", "country": {"23":1,"48":1}},
    {"ID": "03", "country": {"65":1}}
]

result = []
for row in raw_data:
    # 遍历当前行country字典的所有键，逐行写入结果
    for country_code in row["country"].keys():
        result.append({"ID": row["ID"], "country": country_code})

SQL实现 #

不同SQL引擎对Map类型的处理函数略有差异，以下是主流引擎的可直接运行写法：

Spark SQL / Presto / Trino #

用map_keys提取Map的所有键生成数组，再用explode将数组拆为多行，通过LATERAL VIEW和原表ID做关联：

SELECT
    ID,
    country_code AS country
FROM 你的原始表名
LATERAL VIEW explode(map_keys(country)) t AS country_code;

Hive SQL #

Hive中直接explode Map类型会返回键、值两列，直接取键列即可，不需要额外提取键数组：

SELECT
    ID,
    country_code AS country
FROM 你的原始表名
LATERAL VIEW explode(country) t AS country_code, useless_value;

注：上面SQL里的useless_value是explode Map返回的值列（也就是原数据里固定为1的字段），不需要使用直接忽略即可。

ClickHouse #

ClickHouse用mapKeys提取Map键数组，配合arrayJoin完成数组拆行：

SELECT
    ID,
    arrayJoin(mapKeys(country)) AS country
FROM 你的原始表名;

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