从JSON列中提取特定key值生成新列的实现方案

原始数据 #

col1   
[{"key":"1", "count":5, "desc":"blah"},{"key":"2", "count":3, "desc":"meh"}]
[{"key":"2", "count":23, "desc":"qwe"},{"key":"44", "count":0, "desc":""}]

预期结果 #

new_col   
["1", "2"]
["2", "44"]

解决方案 #

用Python Pandas处理 #

如果数据存储在Pandas DataFrame中，分两种场景处理：

1. col1是字符串格式的JSON数组
   先解析JSON字符串为Python列表，再提取每个对象的key字段：

import pandas as pd
import json

# 构造示例DataFrame（替换为你的实际数据读取逻辑）
df = pd.DataFrame({
    'col1': [
        '[{"key":"1", "count":5, "desc":"blah"},{"key":"2", "count":3, "desc":"meh"}]',
        '[{"key":"2", "count":23, "desc":"qwe"},{"key":"44", "count":0, "desc":""}]'
    ]
})

# 生成new_col
df['new_col'] = df['col1'].apply(lambda x: [item['key'] for item in json.loads(x)])

2. col1已经是Python列表（非字符串）
   直接遍历列表提取每个字典的key即可：

df['new_col'] = df['col1'].apply(lambda x: [item['key'] for item in x])

用SQL（以PostgreSQL为例） #

如果数据存储在数据库中，且col1为jsonb类型，可通过内置JSON函数实现：

SELECT 
    col1,
    ARRAY_AGG(obj->>'key') AS new_col
FROM your_table,
     jsonb_array_elements(col1) AS obj
GROUP BY col1;

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