如何使用Oracle SQL生成按INSTITUTION嵌套的JSON数据？

要实现按唯一INSTITUTION值嵌套数据，核心是用数据库的JSON聚合函数，将每个机构对应的组织数据打包成数组结构。以下是主流数据库的实现代码：

PostgreSQL 版本 #

SELECT 
    oo.campus_desc AS INSTITUTION,
    json_agg(
        json_build_object(
            'ORGANIZATION', f.organization_level_6,
            'ORG_DESC', f.organization_mc_desc,
            'DEPARTMENT', min(f.dept)
        )
    ) AS ORGANIZATIONS
FROM odsmgr.campus_official_org_all f
INNER JOIN odsmgr.campus_official_organization oo
    ON f.dept = oo.organization_code
GROUP BY oo.campus_desc

MySQL 版本 #

SELECT 
    oo.campus_desc AS INSTITUTION,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'ORGANIZATION', f.organization_level_6,
            'ORG_DESC', f.organization_mc_desc,
            'DEPARTMENT', MIN(f.dept)
        )
    ) AS ORGANIZATIONS
FROM odsmgr.campus_official_org_all f
INNER JOIN odsmgr.campus_official_organization oo
    ON f.dept = oo.organization_code
GROUP BY oo.campus_desc

SQL Server 版本 #

SELECT 
    oo.campus_desc AS INSTITUTION,
    (
        SELECT 
            f.organization_level_6 AS ORGANIZATION,
            f.organization_mc_desc AS ORG_DESC,
            MIN(f.dept) AS DEPARTMENT
        FROM odsmgr.campus_official_org_all f
        INNER JOIN odsmgr.campus_official_organization oo_inner
            ON f.dept = oo_inner.organization_code
        WHERE oo_inner.campus_desc = oo.campus_desc
        GROUP BY f.organization_level_6, f.organization_mc_desc
        FOR JSON PATH
    ) AS ORGANIZATIONS
FROM odsmgr.campus_official_organization oo
GROUP BY oo.campus_desc

说明 #

• 原SQL按INSTITUTION+ORGANIZATION+ORG_DESC分组返回单条记录，修改后改为仅按INSTITUTION分组，将每组内的组织数据聚合为JSON数组。
• 返回结果中，每个INSTITUTION对应一个ORGANIZATIONS数组，数组内包含该机构下所有组织的ORGANIZATION、ORG_DESC和DEPARTMENT信息。

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