Join查询重复行的通用型Java后置嵌套分组实现方案问询

必须遵循的规则 #

• 不采用纯SQL或数据库层面的处理方式
• 代码需具备通用性，支持任意层级的Join/嵌套结构，而非仅针对当前示例
• 可使用伪代码或任意语言（优先Java），需附带清晰的流程注释
• 不使用Hibernate方案
• 尽可能高效，优先在首次迭代数据时进行动态分组，而非Java Stream事后处理

问题背景 #

Join查询返回重复行数据，定义关联关系如下：

let relations = {
    'account_3.id -> accountshop_5.account' : 'shops',
    'accountshop_5.shop -> shoptext_9.shop' : 'texts'
}

目标是逐行、逐列迭代数据，生成嵌套式的结构化结果，示例期望输出：

[
    { /* account_3 */
        
        "id": "account.1",
        
        "shops": [ /* accountshop_5 */
        
            {
                "id"      : "account.1 ::shop.1",
                "account" : "account.1",
                "shop"    : "shop.1",
                
                "texts": [ /* shoptext_9 */
                    {
                        "id"  : "shop.1 :: txt.1",
                        
                        "shop": "shop.1",
                        "txt" : "This is txt 1"
                    },
                    
                    {
                        "id"  : "shop.1 :: txt.2",
                        
                        "shop": "shop.1",
                        "txt" : "This is txt 2"
                    },
                    
                    {
                        "id"  : "shop.1 :: txt.3",
                        
                        "shop": "shop.1",
                        "txt" : "This is txt 3"
                    }
                ]
            },
            
            {
                "id"      : "account.1 ::shop.2",
                "account" : "account.1",
                "shop"    : "shop.2",
                
                "texts": [ /* shoptext_9 */
                    {
                        "id"  : "shop.2 :: txt.7",
                        
                        "shop": "shop.2",
                        "txt" : "This is txt 7"
                    },
                    
                    {
                        "id"  : "shop.2 :: txt.8",
                        
                        "shop": "shop.2",
                        "txt" : "This is txt 8"
                    },
                    
                    {
                        "id"  : "shop.2 :: txt.9",
                        
                        "shop": "shop.2",
                        "txt" : "This is txt 9"
                    }
                ]
            }
        ]
    }
]

现有尝试及问题 #

尝试过Java逐行收集数据和Stream groupingBy，但未能生成符合预期的嵌套对象。以下是可直接运行的Java测试代码：

public static void main(String[] args) {
    String[]   keys = new String[]{ 
                     "club.id",        /***/ "club.country",  /***/  "fan.id", /***/ "fan.age" };
    String[][] rows = new String[][]{  /***/                  /***/            /***/            
                                       /***/                  /***/            /***/            
        new String[]{ "barca",         /***/ "spain ",        /***/  "peter" , /***/ "18",     },  
        new String[]{ "barca",         /***/ "spain ",        /***/  "peter" , /***/ "18",     },  
        new String[]{ "barca",         /***/ "spain ",        /***/  "peter" , /***/ "18",     },  
        new String[]{ "barca",         /***/ "spain ",        /***/  "jonas" , /***/ "19",     },  
        new String[]{ "barca",         /***/ "spain ",        /***/  "jonas" , /***/ "19",     },  
        new String[]{ "barca",         /***/ "spain ",        /***/  "jonas" , /***/ "19",     },
                                                                             
        new String[]{ "real",          /***/ "spain ",        /***/  "sara"  , /***/ "80",     },  
        new String[]{ "real",          /***/ "spain ",        /***/  "sara"  , /***/ "80",     },  
        new String[]{ "real",          /***/ "spain ",        /***/  "sara"  , /***/ "80",     },  
        new String[]{ "real",          /***/ "spain ",        /***/  "sara"  , /***/ "90",     },  
        new String[]{ "real",          /***/ "spain ",        /***/  "sara"  , /***/ "90",     },  
        new String[]{ "real",          /***/ "spain ",        /***/  "sara"  , /***/ "90",     }
          
    };
    
    List<Map<String, Object>> list = new ArrayList<>();
    for (String[] row : rows) {
        Map<String, Object> map = new HashMap<>();
    
        int i = 0; for (String key : keys) {
            map.put(key, row[i++]);
        }
        
        list.add(map);
    }

    // System.out.println(Gsons.PUBLICS_PRETTY.toJson(list));
 
    String[] groupsBy = new String[]{ "club.id", "fan.id", "fan.age" }; 
    String[] namesBy  = new String[]{ "clubs"  , "fans"  , "ages"    };

    Object o = list.stream()
        .map(row -> {
            return row;                    
        })
        .collect(
            
            groupingBy(row -> { return row.get(groupsBy[0]); },
                groupingBy(row -> { return row.get(groupsBy[1]); },
                        groupingBy(row -> { return row.get(groupsBy[2]); } 
                    ) 
                ) 
            )
        )
    ;

    // System.out.println(Gsons.PUBLICS_PRETTY.toJson(o));;
}

当前使用Stream groupingBy得到的结果不符合预期，期望生成如下嵌套结构：

[
     {
         "clubs" : [
             {
                 "club.id"      : "barca",
                 "club.country" : "spain",
                 "fans" : [
                     {
                         "fan.id"  : "peter", 
                         "ages"    : [
                             {
                                 "fan.age" : 18
                                 // Nothing more to add here, but in theory possible
                             },
                             {
                                 "fan.age" : 19
                                 // Nothing more to add here, but in theory possible
                             }
                         ]
                     },
                 ]
             },
             
             {
                 "club.id" : "real", 
                 "fans" : [
                     {
                         "fan.id"  : "peter", 
                         
                         "ages"    : [
                             {
                                 "fan.age" : 18
                                 // Nothing more to add here, but in theory possible
                             },
                             {
                                 "fan.age" : 19
                                 // Nothing more to add here, but in theory possible
                             }
                         ]
                     }
                 ]
             }
         ]
     }
]

内容的提问来源于stack exchange，提问作者mjs