Postgresql：如何將GROUPBY作為關系表作為Json物件

我有三個表叫User，Post和Category他們有這樣的資料：

用戶表

 id | first_name | second_name 
  1     John          Doe
  2     Ellis         Mount               

帖子表

 id |   topic        |    body        |   author_id    |  category_id 
  1    Internet       ...some text          1                 1
  2    Web 3.0        ...some text          2                 1
  3    Consumer risk  ...some text          1                 2

分類表

 id |   type 
  1    Business     
  2    Technology         

那么，我的問題是怎么樣怎么樣組posts通過category_id與author它的用戶里面為JSON /物件。例如...這就是我希望獲取資料的方式。

[
   technology: [
     { 
        id:1,
        topic: Internet,
        body: ...some text, 
        category_id: 1,
        author_id: 1
        author: {
          first_name: John,
          last_name: Doe
        }
     },
     { 
        id: 2,
        topic: Web 3.0,
        body: ...some text,
        category_id: 1, 
        author_id: 2
        author: {
          first_name: Ellis,
          last_name: Mount
        }
     }
   ],
   Business: [
     { 
        id:3,
        topic: Consumer risk,
        body: ...some text,
        category_id: 2, 
        author_id: 1,
        author: {
          first_name: John,
          last_name: Doe
        }
      },
    ],

]

所以，為了達到這個目的，我用`join嘗試了這樣的事情：

SELECT
    topic,
    body,
    category_id,
    json_build_object('first_name', max(first_name), 'second_name', max(second_name)) AS author,
FROM
    Post p
    JOIN User u ON p.author_id = u.id
GROUP BY
    category_id;  

不幸的是，這沒有獲取我想要的資料......所以如果有人知道如何查詢，請在這里幫助我！

uj5u.com熱心網友回復：

有點復雜的“編織”JSON 結構。我希望它可以直接閱讀。

select jsonb_object_agg(category, j)
from
(
 select category, jsonb_agg(to_jsonb(t) - 'category') j 
 from
 (
  select c."type" category, p.id, p.topic, p.body, p.category_id, p.author_id,
     jsonb_build_object('first_name',u.first_name,'second_name',u.second_name) author
  from posts p
  join category c on p.category_id = c.id
  join users u on p.author_id = u.id
 ) t 
 group by category
) t;

資料庫小提琴

uj5u.com熱心網友回復：

這可以使用嵌套子查詢來完成，如下所示：

SELECT json_object_agg(type, C)
FROM (
SELECT type,
      (SELECT jsonb_agg(Category)
       FROM (SELECT id, topic, body, category_id, author_id,
                    (SELECT to_jsonb(Users)
                     FROM (SELECT first_name, second_name
                           FROM Users
                           WHERE id = Posts.author_id
                    ) AS Users) As author
            FROM Posts
            WHERE category_id=Category.id) As Category) As C
FROM Category 
ORDER BY type) As T

輸出：

{
  "Business": [
    {
      "id": 1,
      "body": "...some text",
      "topic": "Internet",
      "author": {
        "first_name": "John",
        "second_name": "Doe"
      },
      "author_id": 1,
      "category_id": 1
    },
    {
      "id": 2,
      "body": "...some text",
      "topic": "Web 3.0",
      "author": {
        "first_name": "Ellis",
        "second_name": "Mount"
      },
      "author_id": 2,
      "category_id": 1
    }
  ],
  "Technology": [
    {
      "id": 3,
      "body": "...some text",
      "topic": "Consumer risk",
      "author": {
        "first_name": "John",
        "second_name": "Doe"
      },
      "author_id": 1,
      "category_id": 2
    }
  ]
}

標籤：数据库 PostgreSQL的

上一篇：Postgres：優化查詢

下一篇：在多個Join的情況下，如何知道哪個JOIN需要更多時間？