python - 如何在postgres中将两个相似的JSON树合并成一个带有数组的大胖树？

Question

所以我在 Postgres DB 中有两个 JSON 树：

 item_id |             jsonb_pretty              
---------+---------------------------------------
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.2",    +
         |             "item_id": 6             +
         |         }                            +
         |     }                                +
         | }
       2 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.1",    +
         |             "item_id": 3,            +
         |             "children": {            +
         |                 "title": "LEVEL 4.1",+
         |                 "item_id": 4         +
         |             }                        +
         |         }                            +
         |     }                                +
         | }

我想在 Postgres 中将这些树合并为一棵，以便具有相同键的元素变成多个元素的数组。例子：

item_id |             jsonb_pretty   
3   {
    "title": "PARENT",
    "item_id": 1,
    "children": [{
        "title": "LEVEL 2",
        "item_id": 2,
        "children": [{
            "title": "LEVEL 3.2",
            "item_id": 6
        },
        {
            "title": "LEVEL 3.1",
            "item_id": 3,
            "children": {
                "title": "LEVEL 4.1",
                "item_id": 4
            }
        }]
    }]
}

如何在 Postgres 13 中做这样的事情？

标签： pythonsqljsonpostgresqltree