如何关联users表jsonb数组字段与products表查询购物车商品

我有一张users表，其中包含类型为jsonb数组的cart列，示例数据如下：

[{"product_id": 1, "qty": 2}, 
 {"product_id": 2, "qty": 3}]

另有一张products表，主键为product_id，需要查询出users表cart列中所有文档对应的商品信息。

使用PostgreSQL的jsonb_array_elements函数拆分JSONB数组为行记录，再与products表关联即可获取对应商品信息。

完整查询语句 #

SELECT 
    u.user_id, -- 替换为users表实际的用户标识字段
    (cart_item->>'product_id')::integer AS product_id,
    (cart_item->>'qty')::integer AS quantity,
    p.*
FROM 
    users u,
    jsonb_array_elements(u.cart) AS cart_item
INNER JOIN 
    products p ON p.product_id = (cart_item->>'product_id')::integer;

关键说明 #

• jsonb_array_elements(u.cart)：将cart列的JSONB数组拆分为多行，每行对应数组中的一个购物车商品对象
• cart_item->>'product_id'：从JSON对象中提取product_id的文本值，通过::integer转换为整数类型，与products表的主键类型匹配
• 可根据实际需求调整返回字段，比如去掉用户标识、只保留商品核心信息等

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