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

编程语言: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数据遍历分组方法。

3个回答

直接看代码

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!

相似问题

2
sqlserver如何查询所有数据库中包含某段字符串的存储过程名,结果展示为数据库名字-存储过程名
1
如何把数据库读出来的List<Object> 转成特殊结构的json
2
SQL语句中关于查询出来的数据替换的问题?
1
ajax如何将这组json数据遍历出来?
3
这个sql语句怎么写,sql语句如何补齐为空的数据
1
mybatis 根据第一条sql语句查询的结果决定是否第二次查询?
1
jpa 查询日期范围内,sql语句怎么写,日期转换成字符串传过去要跟数据库的日期格式对应吗
3
用$.each处理从后台取到的json数据后该如何绑定到combobox
5
sql语句中时间格式为时分秒,怎么查询某天的数据
4
sql server 指定查询日期范围 如果没数据的当天数据为空而不是没有查询结果怎么做
2
mysql关联表查询如何返回像json格式的数据呢?
1
sql触发器 调用java Servlet地址 传参 后台重新去查 数据库表值 查询结果返回是NULL
1
ASP从数据库直接输出json
1
数据库结果集转json格式但是没办法打印出来,是什么情况?
0
sqlserver数据库查询超时问题
0
SQL查询的数据集分列显示
4
ajax获取不到php从数据库里面读取到的数据,(php读取到的数据已经成功转换成了json数据)
1
mysql5.6以下怎么查询指定字段中json中的值
0
如何为SQL存储过程的参数赋值为另一张表查询的结果集,让存储过程按照这个结果集依次执行所有结果集