PostgreSQL：将JSONB格式手机号数组拆分为列并关联ID

表结构与测试数据 #

CREATE TEMP TABLE IF NOT EXISTS user_info (
    id serial,
    phone_numbers jsonb
);
INSERT INTO user_info values (1, '["123456"]'),(2, '["564789"]'), (3, '["564747", "545884"]');

需求说明 #

需要将user_info表中phone_numbers字段的JSONB数组拆分为单独行，每行对应原表的id，期望结果如下：

错误尝试的SQL #

select s.phone_numbers
from (
        select id,phone_numbers from sales_order_details,
         lateral jsonb_array_elements(phone_numbers) e
     ) s
group by s.phone_numbers

错误原因分析 #

• 引用了不存在的表sales_order_details，应使用目标表user_info
• 未正确提取jsonb_array_elements返回的数组元素，反而使用了原数组字段
• 多余的group by会聚合数据，无法得到拆分后的所有行

正确SQL实现 #

写法一（隐式LATERAL关联） #

SELECT 
    e.phone_number AS phone_numbers,
    u.id
FROM 
    user_info u,
    LATERAL jsonb_array_elements(u.phone_numbers) AS e(phone_number);

写法二（显式JOIN LATERAL） #

SELECT 
    e.phone_number AS phone_numbers,
    u.id
FROM 
    user_info u
JOIN LATERAL 
    jsonb_array_elements(u.phone_numbers) AS e(phone_number) ON true;

说明 #

• jsonb_array_elements函数会将JSONB数组拆分为多行记录
• LATERAL关键字确保每行原表数据都能和拆分后的数组元素关联
• 直接提取函数返回的元素作为单独的手机号字段，同时保留原表的id，保证关联关系正确

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