sql - Postgres：将对象图转换为 JSON

Question

是否有将 Postgres 对象图转换为嵌套 JSON 树的过程？

语境：

• 一个作者有很多书。
• 一本书有很多评论。

下面的查询获取所有作者的所有书籍的所有评论：

select author.id, book.title, review.text
from author
left join book on author.id = book.author_id
left join review on book.id = review.book_id;

使用我的测试数据集，这将返回：

id  title                       text
1   Oathbringer                 obath
1   Oathbringer                 oath
1   The Way of Kings            a
1   The Way of Kings            bye
1   The Way of Kings            hi
2   The Eye of the World        w
3   The Fellowship of the Ring  x

表格格式不方便，所以我将其转换为 json：

select json_agg(sub2.*) as data from (
  select sub.id, sub.title, json_agg(sub.text) as review from (
    select author.id, book.title, review.text
    from author
    left join book on author.id = book.author_id
    left join review on book.id = review.book_id
  ) as sub group by sub.id, sub.title
) as sub2;

数据被提取为

[  
   {  
      "id":1,
      "title":"Oathbringer",
      "review":[  
         "obath",
         "oath"
      ]
   },
   {  
      "id":1,
      "title":"The Way of Kings",
      "review":[  
         "a",
         "bye",
         "hi"
      ]
   },
   {  
      "id":2,
      "title":"The Eye of the World",
      "review":[  
         "w"
      ]
   },
   {  
      "id":3,
      "title":"The Fellowship of the Ring",
      "review":[  
         "x"
      ]
   }
]

不幸的是，查询不正确，因为作者的书籍没有得到聚合。为了纠正这个问题，我写了这个查询：

select sub2.id, json_agg((sub2.title, sub2.review)) as data from (
  select sub.id, sub.title, json_agg(sub.text) as review from (
    select author.id, book.title, review.text
    from author
    left join book on author.id = book.author_id
    left join review on book.id = review.book_id
  ) as sub group by sub.id, sub.title
) as sub2 group by sub2.id;

但是json键搞砸了（'f1'而不是'title'，'f2'而不是'review'）。

问题：

• 如何编写正确的查询？

• 您可以修改json_agg(sub2.*)以明确列出要聚合的列吗？最明显的方式，例如json_agg((sub2.title, sub2.review))使用无意义的键 'f1'、'f2' 等创建 json 对象。

• 有没有更好的方法将对象图转换为 JSON？正确嵌套查询以及分组和聚合字段很难做到正确。在给定对象图的情况下，您将如何编写一个生成返回 JSON 的 SQL 查询的程序？

标签： sqljsonpostgresql