帶有動態 0 級密鑰的 json_agg

我正在將模式從 NoSQL 遷移到 postgres。今天的對象“ b”作為對象的一個元素存在於 NoSQL 中，並且a在原始碼中看起來像這樣……

{
 "a": {
   "id": 1,
   "b":{
     "c1": {
       "d1": 1,
       "d2": 2
     },
     "c2": {
       "d1": 1,
       "d2": 2
     },
     "c3": {
       "d1": 3,
       "d2": 4
     }
   }
 }
}

…我暫時將其分配給類似於以下 ERD 的模式

+---+    +-----+    +---+    +---+
| b |<--<| b_d |>-->| d |>-->| c |
+---+    +-----+    +---+    +---+

完整的 db_fiddle

我希望能夠將規範化數據重新組合成原始對象，但我一直無法這樣做。我沒有將“ c”對像作為直接鍵值對獲取，而是獲取整個對象。例如…

select 
   b_d.b_id, 
   json_agg(json_build_object(c.name,json_build_object('d1',d.d1,'d2',d.d2))) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

…返回…

[{
   "c1 ": {
       "d1": 1,
       "d2": 2
   }
},
{
   "c2 ": {
       "d1": 1,
       "d2": 2
   }
},
{
   "c3 ": {
       "d1": 3,
       "d2": 4
   }
}]

…您會注意到，與源對象相比，它具有額外的嵌套級別。我懷疑我一開始就出錯了，json_build_object但到目前為止還無法成功地重建b原始 json 文件中出現的“”對象。

我應該對查詢和/或底層架構進行哪些修改以規範化和非規範化該對象？

不要查看 json 函式文件頁面，而是查看聚合函式文件頁面。如本展示jsonb_object_agg()所示，將聚合對象而無需額外的嵌套級別。

select 
   b_d.b_id,
   jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2)) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

…根據需要返回原始對象。

{
   "c1 ": {
       "d1": 1,
       "d2": 2
   },
   "c2 ": {
       "d1": 1,
       "d2": 2
   },
   "c3 ": {
       "d1": 3,
       "d2": 4
   }
}

另請 參閱

引用自：https://dba.stackexchange.com/questions/253547