PostgreSQL如何提取JSON列多字典中action_detail值（忽略key=0）

实现思路 #

1. 将JSON对象拆分为键值对行：使用json_each()（若列类型为jsonb则用jsonb_each()）展开顶级JSON结构，每个顶级key对应一行数据
2. 过滤目标条目：排除key为0的记录
3. 提取指定字段：从action_detail中提取SEARCH、ADD_TO_CART、SKU_DETAIL_VIEW的值，用COALESCE()处理缺失字段，返回0而非null

完整SQL查询 #

假设你的表名为your_table，存储JSON的列名为event_tracking（类型为json），执行以下语句：

SELECT
    t.top_key AS id,
    COALESCE((t.item -> 'action_detail' ->> 'SEARCH')::INT, 0) AS search_count,
    COALESCE((t.item -> 'action_detail' ->> 'ADD_TO_CART')::INT, 0) AS add_to_cart_count,
    COALESCE((t.item -> 'action_detail' ->> 'SKU_DETAIL_VIEW')::INT, 0) AS sku_detail_view_count
FROM
    your_table,
    json_each(your_table.event_tracking) AS t(top_key, item)
WHERE
    t.top_key != '0'
ORDER BY
    t.top_key::INT;

关键部分说明 #

• json_each(your_table.event_tracking)：把JSON对象拆分为多行，每行包含顶级key（top_key）和对应的子JSON对象（item）
• t.top_key != '0'：直接过滤掉不需要的key为0的条目
• item -> 'action_detail' ->> 'SEARCH'：先定位到action_detail子对象，再提取SEARCH的字符串值，最后::INT转为整数类型
• COALESCE(..., 0)：如果某个动作类型不存在（比如部分条目没有SKU_DETAIL_VIEW），用0填充空值
• ORDER BY t.top_key::INT：按id的数值排序，避免字符串排序导致的顺序混乱（比如'6'排在'16'之前）

若你的JSON列类型是jsonb，只需将json_each替换为jsonb_each即可。

内容的提问来源于stack exchange，提问作者Mr.Bean