How to fetch one to many relation data using single query in postgre sql

Question

I have two tables category and sub_category

category

category_id	name
1	category1
2	category2

sub_category

sub_category_id	name	category_id
1	subcategory1	1
2	subcategory2	1

i want to get data in the following format:

{

  category:{
         category_id:1,
         name:category1
         }
  subCategories:[
               {
                subCategory_id:1
                name:subcategory1
               },
               {
                subCategory_id:2
                name:subcategory2
                },
             ]
}

currently i am getting data using left join and then manually iterating the list to create the above format .It will be helpful if i can the data from database directly in the above format

Answer 1

You can do it using json_build_object to return JSON and the aggregate function json_agg to create subCategories JSON array :

select
  c.category_id,
  json_build_object(
     'category', json_build_object(
                            'category_id', c.category_id,
                            'name', c.name
                    ),
     'subCategories',  json_agg(
                             json_build_object(
                                  'subCategory_id', sc.sub_category_id,
                                   'name', sc.name
                             ))
                        
  ) as json_
from category c
inner join sub_category sc on sc.category_id = c.category_id
group by c.category_id, c.name

Result :

category_id json_
1          {
           "category":{
              "category_id":1,
              "name":"category1"
           },
           "subCategories":[
              {
                 "subCategory_id":2,
                 "name":"subcategory2"
              },
              {
                 "subCategory_id":1,
                 "name":"subcategory1"
              }
           ]
        }

Demo here