JSON列拆分提取时状态字段重复显示的SQL问题求助

嘿，我来帮你解决这个问题！先理清楚你的需求：你需要把outgoing列里的JSON键值对拆分成单独的行，同时提取每个键末尾的数字作为amount，对应的值作为status，还要保留原表的id字段对吧？

原始表数据 #

你期望的结果 #

你现有查询的问题 #

你写的查询里，substring(outgoing::varchar from ':"([a-z]*)"' )这个正则表达式是从整个JSON字符串里匹配第一个符合格式的状态值，所以不管json_object_keys拆出来哪个键，都会返回同一个状态，这就是重复的原因。

正确的SQL解决方案 #

我们需要针对每个拆分出来的键，直接获取它对应的JSON值作为status，而不是从整个字符串里提取。同时提取键末尾的数字作为amount，还要保留原表的id：

SELECT 
    t.id,
    t.outgoing,
    t.outgoing->>j.key AS status,
    SUBSTRING(j.key FROM '_(\d+)$')::INTEGER AS amount
FROM table1 t
CROSS JOIN LATERAL json_object_keys(t.outgoing) j(key);

解释一下关键点 #

1. t.outgoing->>j.key：通过拆分出来的键j.key直接获取对应的JSON字符串值，这样每个行的status都是当前键对应的正确状态，不会重复。
2. SUBSTRING(j.key FROM '_(\d+)$')::INTEGER：用正则匹配键末尾的数字部分，转换为整数类型作为amount，比你原来的写法更精准，只匹配下划线后的数字。
3. 保留id和原outgoing列：确保结果和你期望的结构一致。

测试这个查询后，就能得到你想要的每行对应正确状态和金额的结果啦！

备注：内容来源于stack exchange，提问作者manlike