如何改写SQL查询实现Profil关联数据聚合并生成指定JSON结构

原SQL查询 #

SELECT pr.* as profil, (select count(*) from t_employe e where e.profil_id = pr.id) as nbEmploye, f.* as fonctionnalites
FROM t_profil pr, t_fonctionnalite f
INNER JOIN t_profils_fonctionnalites pf ON  pf.fonctionnalite_id = f.id 
WHERE pf.profil_id = pr.id
GROUP BY f.id, pr.id

当前查询结果示例 #

1   "CODEP1"    "descriptionP1" "labelP1"       20      1   "codeF1"    "descriptionF1" "labelF1"
2   "CODEP2"    "descriptionP2" "labelP2"       1       1   "codeF1"    "descriptionF1" "labelF1"
1   "CODEP1"    "descriptionP1" "labelP1"       20      2   "codeF2"    "descriptionF2" "labelF2"
...

期望的聚合展示效果 #

1   "CODEP1"    "descriptionP1" "labelP1"       20      1   "codeF1"    "descriptionF1" "labelF1"
                                                        2   "codeF2"    "descriptionF2" "labelF2"
2   "CODEP2"    "descriptionP2" "labelP2"       1       1   "codeF1"    "descriptionF1" "labelF1"
...

最终目标JSON结构 #

[
  {
    "profil": {
                "code": "CODEP1",
                "label": "LABELP1",
                "description": "DESCRIPTIONP1",
              },
    "nbEmploye": 20,
    "fonctionalites": [
       {
                "code": "CODEF1",
                "label": "LABELF1",
                "description": "DESCRIPTIONF1",
       },  
       {
                "code": "CODEF2",
                "label": "LABELF2",
                "description": "DESCRIPTIONF2",
       },               
    ],
    "employe": {        
                  "name": "........."
               }
  }
]

改写方案（分数据库类型） #

MySQL 8.0+ #

用JSON_ARRAYAGG和JSON_OBJECT直接生成嵌套JSON结构：

-- 生成目标格式的JSON数组
SELECT JSON_ARRAYAGG(profil_json) AS final_json
FROM (
  SELECT
    JSON_OBJECT(
      'profil', JSON_OBJECT(
        'code', pr.code,
        'label', pr.label,
        'description', pr.description
      ),
      'nbEmploye', (SELECT COUNT(*) FROM t_employe e WHERE e.profil_id = pr.id),
      'fonctionalites', JSON_ARRAYAGG(
        JSON_OBJECT(
          'code', f.code,
          'label', f.label,
          'description', f.description
        )
      ),
      'employe', JSON_OBJECT('name', '.........') -- 若需真实员工数据，自行关联t_employe调整
    ) AS profil_json
  FROM t_profil pr
  JOIN t_profils_fonctionnalites pf ON pr.id = pf.profil_id
  JOIN t_fonctionnalite f ON pf.fonctionnalite_id = f.id
  GROUP BY pr.id, pr.code, pr.label, pr.description
) AS sub;

PostgreSQL #

用jsonb_agg和jsonb_build_object实现聚合：

SELECT
  jsonb_agg(
    jsonb_build_object(
      'profil', jsonb_build_object(
        'code', pr.code,
        'label', pr.label,
        'description', pr.description
      ),
      'nbEmploye', (SELECT COUNT(*) FROM t_employe e WHERE e.profil_id = pr.id),
      'fonctionalites', func_agg,
      'employe', jsonb_build_object('name', '.........')
    )
  ) AS final_json
FROM (
  SELECT
    pr.id, pr.code, pr.label, pr.description,
    jsonb_agg(
      jsonb_build_object(
        'code', f.code,
        'label', f.label,
        'description', f.description
      )
    ) AS func_agg
  FROM t_profil pr
  JOIN t_profils_fonctionnalites pf ON pr.id = pf.profil_id
  JOIN t_fonctionnalite f ON pf.fonctionnalite_id = f.id
  GROUP BY pr.id, pr.code, pr.label, pr.description
) AS sub;

SQL Server #

用FOR JSON PATH语法生成指定结构的JSON：

SELECT
  pr.code AS 'profil.code',
  pr.label AS 'profil.label',
  pr.description AS 'profil.description',
  (SELECT COUNT(*) FROM t_employe e WHERE e.profil_id = pr.id) AS nbEmploye,
  f.code AS 'fonctionalites.code',
  f.label AS 'fonctionalites.label',
  f.description AS 'fonctionalites.description',
  '.........' AS 'employe.name'
FROM t_profil pr
JOIN t_profils_fonctionnalites pf ON pr.id = pf.profil_id
JOIN t_fonctionnalite f ON pf.fonctionnalite_id = f.id
GROUP BY pr.id, pr.code, pr.label, pr.description, f.code, f.label, f.description
FOR JSON PATH, ROOT('');

几点注意 #

1. 原SQL混用了逗号分隔表和INNER JOIN，容易引发数据异常，建议统一用显式JOIN写法。
2. 示例里的employe字段是固定值，如果你需要真实员工数据，得根据业务逻辑关联t_employe表调整（比如聚合员工列表或取特定员工）。
3. 不同数据库的JSON函数语法存在差异，选择和你实际使用的数据库匹配的方案即可。

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