node.js - how to obtain objects in objects (with all the key names) in SQL?

Question

my database contains two tables named category and sport:

category
-----------------------------------
id        |   title    | ...
1         |   summer
2         |   autumn
3         |   spring
4         |   winter

sport
--------------------------------------------------------------
id        |   title       | description | category_id  | ...
1         |   ski         | ...         |   4
2         |   surf        | ...         |   1
3         |   snorkeling  | ...         |   1
4         |   running     | ...         |   3
5         |   hiking      | ...         |   2
...

my question is how to obtain with one request in Sql (postgresql) this result :

{
        category: [
                {
                        c.id: 1,
                        c.title: summer,
                        sport: [
                                {
                                        s.id: 2,
                                        s.title: surf,
                                },
                                {
                                        s.id: 3,
                                        s.title: snorkeling,
                                },
                        ],     
                },
                {
                        c.id: 2,
                        c.title: autumn,
                        sport: [
                                {
                                        s.id: 5,
                                        s.title: hiking
                                }
                        ],
                },
                {
                        c.id: 3
                        c.title: spring,
                        sport: [
                                {
                                        s.id: 4,
                                        s.title: running,
                                }
                        ],
                },
                {
                        ...
                }               
        ]
}

I've tried with ARRAY_AGG but it removes the key_names and i need it to call the values with in my API.

question from:https://stackoverflow.com/questions/65872738/how-to-obtain-objects-in-objects-with-all-the-key-names-in-sql

Answer 1

You need to combine jsonb_agg(), to_jsonb()andjsonb_build_object()`

select to_jsonb(c)||jsonb_build_object('sport', jsonb_agg(to_jsonb(s) - 'category_id'))
from category c
  join sport s on s.category_id = c.id
group by c.id;

The above assumes that category.id is defined as the primary key.

It returns one row per category.

If you really want one gigantic array of all rows, you need to aggregate in two steps:

select jsonb_build_object('category', jsonb_agg(to_jsonb(c)||sport))
from category c
   join (
    select category_id, jsonb_build_object('sport', jsonb_agg(to_jsonb(s) - 'category_id')) as sport
    from sport s
    group by s.category_id
  ) s on s.category_id = c.id

Online example