Postgres/JSON - 将父子关系转换为 json

Question

我在postgres数据库中有以下记录。parent_pk与父子关系中的 pk 相关。

PK 名称类型 parent_pk---            ----             ----            ---------1 H编号101 房屋 02 H编号201 房屋 0101 dodge_charger 车辆 1102 野马车 1103 思域车辆 2301 约翰用户 101302 爱德华用户 102303约翰用户102304 约翰用户 103我想以以下格式从上面生成一个 json-[  {   "id"： 1，   "名称"： "Hnumber101"，   "类型"： "房子"   "孩子"：[ {              "id"： 101，              "名称"： "道奇充电器"，              "类型"： "车辆"              "儿童"：[{                       "ID"： 301，                       "名字"： "约翰"，                       "类型"： "用户"                       }],                                         },              {              "id"： 102，              "名称"： "野马"，              "类型"： "车辆"              "儿童"：[{                       "id"： 303，                       "名字"： "约翰"，                       "类型"： "用户"                       },                       {                       "id"： 302，                       "名字"： "爱德华"，                       "类型"： "用户"                       }],                                         }],  }, {   "id"： 2，   "名称"： "Hnumber201"，   "类型"： "房子"   "孩子"：[ {              "id"： 103，              "名称"： "公民"，              "类型"： "车辆"              "儿童"：[{                       "id"： 304，                       "名字"： "约翰"，                       "类型"： "用户"                       }],              }],  }]

我尝试使用 WITH 递归查询，但这会产生条目列表，然后我必须在我的 java 代码中做很多循环/哈希映射才能在父块中获取该子块。最有效的方法是什么？

Answer 1

大致如下：

SELECT things.pk, things.name, things.type, array_agg(sub.pk) AS children
FROM things
LEFT JOIN things sub ON sub.parent_pk = things.pk
GROUP BY things.pk, things.name, things.type;

 PK |    姓名 | 类型 |孩子 -----+---------------+---------+-----------   1 |编号101 |房子 |{102,101} 101 |道奇充电器 |车辆 |{301} 102 |野马 |车辆 |{303,302} 301 |约翰 |用户 |{空} 302 |爱德华 |用户 |{空} 303 |约翰 |用户 |{空}

将行收集到由 pk Map<Integer, Thing> 键入的地图中。

class Thing {
  Integer id;
  String name;
  String type;
  Integer[] children;
  public String printMe(Map<Integer, Thing> allThings) {
    String ret = "... format json stuff here";
    for(Integer childId in children) {
      Thing child = allThings.get(childId);
      ret += child.printMe(allThings);
    }
    ret += "Format json stuff here";
    return ret;
  } 
};