如何按重复值分组并嵌套数组Postgresql [英] How to group by duplicate value and nested the array Postgresql

问题描述

我想进行查询以给出结果，如果存在重复的id，它将被分组，并且下面的数据将完整地保留到相同的分组数据中，并且全部在数组JSON数据中。

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.

我这样查询：

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"

结果如下。

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

我期望的是这样。

{
    "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"
    }
}

我如何像我的期望那样进行查询？

How could i make a query like my expect ?

推荐答案

您需要打开聚合，并使用 json_agg（）生成正确的数据结构。

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"

这篇关于如何按重复值分组并嵌套数组Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！