I have three tables called User, Post and Category which they have data like this:

Users Table

 id | first_name | second_name 
  1     John          Doe
  2     Ellis         Mount               

Posts Table

 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

Category Table

 id |   type 
  1    Business     
  2    Technology         

So what my question is how to how to group posts by category_id with the author user inside of it as json/object. for example... this is how I want my data to be fetched.

[
   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
        }
      },
    ],

]

So, in a way to reach this, I tried something like this with `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;  

unfortunately, this not fetching the data I want... so if any know how to query this please help me out here!

CodePudding user response：

A bit complicated 'knitting' the JSON structure. I hope that it is straightforward to read.

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;

DB Fiddle

CodePudding user response：

This can be done using nested subqueries as follows:

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

Output:

{
  "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
    }
  ]
}