PostgreSQL中如何基于json_array_elements返回值进行关联查询？

我来帮你梳理下结合你的表结构，怎么用PostgreSQL的jsonb_array_elements（因为你的字段是jsonb类型，比json版本性能更好）来做关联查询，一步步来：

1. 先搞懂核心函数：jsonb_array_elements #

这个函数的作用就是把一个jsonb数组拆成多行，每一行对应数组里的一个元素。比如你Order表的subOrders字段是包含两个Pointer的数组，用它处理后会直接变成两条独立的行记录，每条对应一个子订单的Pointer对象。

2. 关联Order和SubOrder表 #

要把Order和SubOrder关联起来，得先展开subOrders数组，提取里面的objectId，再用这个ID去匹配SubOrder表的objectId。这里必须用LATERAL连接，因为jsonb_array_elements需要引用前面Order表的subOrders字段，LATERAL允许函数/子查询引用外层表的列。

示例SQL如下：

SELECT 
    o.objectId AS order_id,
    so.objectId AS suborder_id,
    so.orderitems AS suborder_items
FROM 
    "Order" o
-- 展开subOrders数组，得到每个子订单的Pointer
CROSS JOIN LATERAL 
    jsonb_array_elements(o.subOrders) AS sub_order_ptr
-- 用Pointer里的objectId关联SubOrder表
JOIN 
    "SubOrder" so ON so.objectId = sub_order_ptr->>'objectId';

3. 进一步关联到SubOrderItem表 #

如果还需要关联SubOrderItem表，只需要对SubOrder的orderitems数组做同样的展开操作，再关联对应的表即可：

SELECT 
    o.objectId AS order_id,
    so.objectId AS suborder_id,
    soi.objectId AS suborder_item_id
FROM 
    "Order" o
CROSS JOIN LATERAL 
    jsonb_array_elements(o.subOrders) AS sub_order_ptr
JOIN 
    "SubOrder" so ON so.objectId = sub_order_ptr->>'objectId'
-- 展开subOrder里的orderitems数组
CROSS JOIN LATERAL 
    jsonb_array_elements(so.orderitems) AS item_ptr
-- 关联SubOrderItem表
JOIN 
    "SubOrderItem" soi ON soi.objectId = item_ptr->>'objectId';

几个关键注意点 #

• 如果你的Order记录可能存在空的subOrders数组（没有子订单），想要保留原Order记录的话，把CROSS JOIN LATERAL换成LEFT JOIN LATERAL，这样没有子订单的行也会被返回，对应的SubOrder字段会是NULL。
• 一定要用->>操作符提取objectId，而不是->：->返回的是jsonb类型，和SubOrder表的text类型objectId不匹配，->>会直接把json值转换成文本类型，避免类型错误。
• 因为你的表名Order是PostgreSQL的保留字，所以查询时必须用双引号包裹，不然会报错。

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