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 ?

You expected output is not valid JSON format. Are you looking to have a JSON array under key "proyek"? — GMB
– GMB, Commented Oct 31, 2019 at 10:42

GMB · Accepted Answer · 2019-10-31 10:51:49Z

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"

answered Oct 31, 2019 at 10:51

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gagantous Over a year ago

Do you know how to group by again no_izin to have nested array for each kode and judul_kode ? i tried to json_agg the no_izin but it didnt work. So in my question i have duplicate no_izin. I want to group by its, so each no_izin have nested array of kode and judul_kode

Collectives™ on Stack Overflow

How to group by duplicate value and nested the array Postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related