sql - How to group by duplicate value and nested the array Postgresql

Question

I want to make a query that will give result, if there is duplicate id it will grouped, and the data below will intact to the same groupped data and it's all in array JSON data.

I make a query like this:

SELECT json_build_object(
    'nama_perusahaan',"a"."nama_perusahaan",
    'proyek', json_build_object(
        'no_izin',"b"."no_izin",
        'kode',c.kode,
        'judul_kode',d.judul
    )
)
FROM "t_pencabutan" "a"
LEFT JOIN "t_pencabutan_non" "b" ON "a"."id_pencabutan" = "b"."id_pencabutan"
LEFT JOIN "t_pencabutan_non_b" "c" ON "b"."no_izin" = "c"."no_izin"
LEFT JOIN "t_pencabutan_non_c" "d" ON "c"."id_proyek" = "d"."id_proyek"

the result is like below.

{
    "nama_perusahaan" : "JASA FERRIE", 
    "proyek" : 
    {
        "no_izin" : "26A/E/IUA/ABC/D8FD", 
        "kode" : "14302", 
        "judul_kode" : "IND"
    }
}
{
    "nama_perusahaan" : "JASA FERRIE", 
    "proyek" : 
    {
        "no_izin" : "26A/E/IUA/ABC/D8FD", 
        "kode" : "13121", 
        "judul_kode" : "IND B"
    }
}

what i expect was like this.

{
    "nama_perusahaan" : "JASA FERRIE", 
    "proyek" : 
    {
        "no_izin" : "26A/E/IUA/ABC/D8FD", 
        "kode" : "14302", 
        "judul_kode" : "IND"
    }
    {
        "no_izin" : "26A/E/IUA/ABC/D8FD", 
        "kode" : "13121", 
        "judul_kode" : "IND B"
    }
}

How could i make a query like my expect ?

Answer 1

You would need to turn on aggregation, and use json_agg() to generate the proper data structure.

This should be close to what you want:

SELECT json_build_object(
    'nama_perusahaan',"a"."nama_perusahaan",
    'proyek', json_agg(
            json_build_object(
            'no_izin',"b"."no_izin",
            'kode',c.kode,
            'judul_kode',d.judul
        )
    )
)
FROM "t_pencabutan" "a"
LEFT JOIN "t_pencabutan_non" "b" ON "a"."id_pencabutan" = "b"."id_pencabutan"
LEFT JOIN "t_pencabutan_non_b" "c" ON "b"."no_izin" = "c"."no_izin"
LEFT JOIN "t_pencabutan_non_c" "d" ON "c"."id_proyek" = "d"."id_proyek"
GROUP BY "a"."nama_perusahaan"