sql - 带有 JSON 对象数组聚合的 LEFT JOIN 查询

Question

我有 2 个表:

person 表，包含列:person_id、person_name
包含列的 pet 表:pet_id、owner_id、pet_name

person data:
1, 'John'
2, 'Jill'
3, 'Mary'

pet data:
1, 1, 'Fluffy'
2, 1, 'Buster'
3, 2, 'Doggy'

如何使用聚合函数从 person left join pet 编写 SELECT 查询 person_id = owner_id我的结果数据如下:

1, [{pet_id:1,pet_name:'Fluffy'},{pet_id:2,pet_name:'Buster'}], 'John'
2, [{pet_id:3,pet_name:'Doggy'}], 'Jill'
3, [],'Mary'

Answer 1

使用 LEFT JOIN LATERAL 并在子查询中聚合:

SELECT p.person_id, COALESCE(pet.pets, '[]') AS pets, p.person_name
FROM   person p
LEFT   JOIN LATERAL (
   SELECT json_agg(json_build_object('pet_id', pet.pet_id
                                   , 'pet_name', pet.pet_name)) AS pets
   FROM   pet
   WHERE  pet.owner_id = p.person_id
   ) pet ON true
ORDER  BY p.person_id;  -- optional, Q suggests ordered results

db<> fiddle here

这样您就不需要聚合来自外部查询的结果。当您的外部查询比问题中的示例更复杂时，更简单和更清晰。当聚合多个相关表时，它甚至成为必需品:

• Multiple array_agg() calls in a single query
• Two SQL LEFT JOINS produce incorrect result

当在外部表 person 上有选择性谓词时，它通常也更快 - 这是典型的用例。

确保 pet(owner_id) 有一个 index 以使其更快。
如果您的行不宽，甚至可以在 Postgres 11 或更高版本中使用 pet(owner_id, pet_id, pet_name) 或 pet(owner_id) INCLUDE (pet_id, pet_name)就像在您的示例中一样，如果您从中获得仅索引扫描。

哦，使用 json_build_object() 为任意选择保留属性 names:

• Return multiple columns of the same row as JSON array of objects

相关:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?