如何将sql查询结果处理为指定格式的JSON数据格式？

编程语言：nodejs+express

sql语句如下：

var sql_02 = `select u.HospitalId as hospitalId,h.HospitalName as hospitalName,u.DeptId as deptId,hd.DeptName as deptName ,UserId as doctorId,UserName as doctorName
                from [user] u
                left join hospital h on h.HospitalId=u.HospitalId
                left join hospital_department hd on hd.DeptId=u.DeptId
                where u.HospitalId=?`

查询结果将返回一个JSON数据；

{
    "success": true,
    "data": [{
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008053",
        "deptName": "内儿科",
        "doctorId": "10080001",
        "doctorName": "*清华"
    }, {
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008050",
        "deptName": "肿瘤血液内科",
        "doctorId": "10080002",
        "doctorName": "*浩"
    }, {
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008055",
        "deptName": "肝胆胃肠三腺外科",
        "doctorId": "10080003",
        "doctorName": "**勇"
    }, {
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008027",
        "deptName": "急诊科",
        "doctorId": "10080004",
        "doctorName": "**彬"
    }, {
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008058",
        "deptName": "呼吸内科",
        "doctorId": "10080005",
        "doctorName": "*伊潇"
    }, {
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008069",
        "deptName": "疼痛科",
        "doctorId": "10080006",
        "doctorName": "**孔"
    }, {
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008069",
        "deptName": "疼痛科",
        "doctorId": "10080007",
        "doctorName": "米*杰"
    }, {
        "hospitalId": "1008",
        "hospitalName": "**县人民医院",
        "deptId": "1008001",
        "deptName": "信息科",
        "doctorId": "10089999",
        "doctorName": "管理员"
    }],
    "message": "数据获取成功"
}

现在需要转换为如下格式：

[{
    "hosptialId": "10001",
    "hosptialName": "**医院",
    "deptItem": [{
            "deptId": "10001001",
            "deptName": "心内科",
            "DoctorItem": [{
                    "doctorId": "10001001001",
                    "doctorName": "张教授"
                },
                {
                    "doctorId": "10001001002",
                    "doctorName": "李教授"
                },
                {
                    "doctorId": "10001001001",
                    "doctorName": "张教授"
                }
            ]
        },
        {
            "deptId": "10001002",
            "deptName": "消化科",
            "DoctorItem": [{
                    "doctorId": "10001002001",
                    "doctorName": "王教授"
                },
                {
                    "doctorId": "10001002002",
                    "doctorName": "陈教授"
                },
                {
                    "doctorId": "10001002001",
                    "doctorName": "高教授"
                }
            ]
        }
    ]
}, {
    "hosptialId": "10002",
    "hosptialName": "**附属医院",
    "deptItem": [{
            "deptId": "10002001",
            "deptName": "心胸外科",
            "DoctorItem": [{
                    "doctorId": "10002001001",
                    "doctorName": "范教授"
                },
                {
                    "doctorId": "10002001002",
                    "doctorName": "孙教授"
                },
                {
                    "doctorId": "10002001001",
                    "doctorName": "高教授"
                }
            ]
        },
        {
            "deptId": "10002002",
            "deptName": "肿瘤科",
            "DoctorItem": [{
                    "doctorId": "10002002001",
                    "doctorName": "王教授"
                },
                {
                    "doctorId": "10002002002",
                    "doctorName": "雷教授"
                },
                {
                    "doctorId": "10002002003",
                    "doctorName": "杨教授"
                }
            ]
        }
    ]
}]

也就是说，目标JSON格式是医院-科室-医生。
求JSON数据遍历分组方法。

https://blog.csdn.net/qq_38025219/article/details/82759926

直接看代码

f = open('product.json',encoding='utf-8')
res = f.read()
product_dic = json.loads(res)  #把json串，变成python的数据类型，然后遍历dict就行了呀
f.close()

node.js 中数据库操作后读取的每条记录安装你这里的格式进行转换后返回给调用者的。
https://blog.csdn.net/wojiushiwo945you/article/details/87633001