How to group by duplicate value of nested array in Postgresql?

Question

Previously question : How to group by duplicate value and nested the array Postgresql

Using this query :

    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"

The result is shown below:

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

As you could see, the proyek have been nested, so the duplicate proyek will be grouped. Now i have to group the same value of no_izin so it will double nested array like expected result below.

{
    "nama_perusahaan" : "JASA FERRIE", 
    "proyek" : 
    [{
        "no_izin" : "26A/E/IU/PMA/D8FD", 
        "kode_list":[
         {
              "kode" : "14302", 
              "judul_kode" : "IND"
         },
         {
              "kode" : "13121", 
              "judul_kode" : "IND B"
         }]   
    }]
}

I tried to use this query:

SELECT json_build_object(
    'nama_perusahaan',"a"."nama_perusahaan",
    'proyek', json_agg(
            json_build_object(
            'no_izin',"b"."no_izin",
            'kode_list',json_agg(
                  json_build_object(
                       '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", b.no_izin

but it didnt work, it gives ERROR: aggregate function calls cannot be nested LINE 6:'kode_list',json_agg(. What could go wrong with my code ?

Gagantous · Accepted Answer · 2019-11-01 10:29:31Z

Disclaimer: It is very hard for us to construct a query without knowing the input data and table structure and have to handle a language we don't know. Please try to minimize your further questions (e.g. For your question it is not relevant that you need to join some tables before converting the result into a JSON output), create examples in English (handling foreign languages makes the code looking confusing and leads to spelling errors, so the probably right idea fails on writing the words wrong) and add the input data! This would help you as well: You would get an answer faster and the chance of code mistakes is much more less (because now without the data we cannot create a runnable example to check our ideas).

Creating a nested JSON structure is only possible doing it from the innermost nested object to the outermost one. So first you have to create the no_izin array in a subquery. This can be used to create the proyek object:

SELECT 
    json_build_object(
        'nama_perusahaan',"s"."nama_perusahaan",
        'proyek', json_agg(no_izin)
    )
)
FROM (
    SELECT
        "a"."nama_perusahaan",
        json_build_object(
            'no_izin',
            "b"."no_izin",
            'kode_list',
            json_agg(
                json_build_object(
                    'kode',c.kode,
                     'judul_kode',d.judul
                )
            )
        ) AS no_izin
    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 "c"."id_proyek", "a"."nama_perusahaan"
) AS s
GROUP BY "s"."nama_perusahaan"

thanks! this one works! My apologize with my question, i cant explain my problem so well, lack of sample data, and such... hopefully, i could ask some better next times. Again, thank you very much

Collectives™ on Stack Overflow

How to group by duplicate value of nested array in 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