时间戳

首页 > 解决方案 > 嵌套 JSON 元素

python - 嵌套 JSON 元素

问题描述

我编写了一个 Python 程序，在其中通过连接从 SQL 中提取数据，然后将其转换为 JSON。

我的带有 JSON 转换的 SQL 查询是

"SELECT CAST( (SELECT p.[Pname],p.[Pnumber] ,d.[Dname], e.[fname] as 'Employee.fname'
    , e.[lname] as 'Employee.lname', w.[hours] as 'Employee.hours' 
FROM [dbo].[PROJECT] p
join [dbo].[DEPARTMENT] d on p.[dnum] = d.[dnumber]
join [dbo].[works_on] w on w.[pno] = p.[pnumber]
join [dbo].[employee] e on e.[ssn] = w.[essn]
for json path) AS TEXT)"

样本输出是

[{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"John","lname":"Smith","hours":"32.5"}},
{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"Joyce","lname":"English","hours":"20"}},
{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"Roy","lname":"Lewallen","hours":"2"}},
{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"Jisha","lname":"Carpenter","hours":"25"}},
{"Pname":"ProductY","Pnumber":2,"Dname":"Research","Employee":{"fname":"John","lname":"Smith","hours":"7.5"}},
{"Pname":"ProductY","Pnumber":2,"Dname":"Research","Employee":{"fname":"Joyce","lname":"English","hours":"20"}},
.......

我需要像这样的输出（将属于同一项目的所有雇员分组到一个列表中）：

[{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee": 
   [
   {"fname":"John","lname":"Smith","hours":"32.5"}, 
   {"fname":"Joyce","lname":"English","hours":"20"}, 
   {"fname":"Roy","lname":"Lewallen","hours":"2"}, 
   {"fname":"Jisha","lname":"Carpenter","hours":"25"}
   ]
},
{"Pname":"ProductY","Pnumber":2,"Dname":"Research","Employee": 
   {"fname":"John","lname":"Smith","hours":"7.5"},...............
   ]

标签： pythonsqljsonsql-serverflask

解决方案

input_list = [{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"John","lname":"Smith","hours":"32.5"}},
{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"Joyce","lname":"English","hours":"20"}},
{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"Roy","lname":"Lewallen","hours":"2"}},
{"Pname":"ProductX","Pnumber":1,"Dname":"Research","Employee":{"fname":"Jisha","lname":"Carpenter","hours":"25"}},
{"Pname":"ProductY","Pnumber":2,"Dname":"Research","Employee":{"fname":"John","lname":"Smith","hours":"7.5"}},
{"Pname":"ProductY","Pnumber":2,"Dname":"Research","Employee":{"fname":"Joyce","lname":"English","hours":"20"}}]

output_list = []
pnum_list   = []
for item in input_list:
    if item['Pnumber'] in pnum_list:
        for i in range(len(output_list)):
            if output_list[i]["Pnumber"] == item['Pnumber']:
                output_list[i]['Employee'].append(item['Employee'])
                break
    else:
        output_list.append({
          "Pname":item['Pname'],
          "Pnumber":item['Pnumber'],
          "Dname":item['Dname'],
          "Employee": [item['Employee']]
        })
        pnum_list.append(item['Pnumber'])


print(output_list)


[
  {'Pname': 'ProductX', 'Pnumber': 1, 'Dname': 'Research', 'Employee': [
    {'fname': 'John', 'lname': 'Smith', 'hours': '32.5'}, 
    {'fname': 'Joyce', 'lname': 'English', 'hours': '20'}, 
    {'fname': 'Roy', 'lname': 'Lewallen', 'hours': '2'}, 
    {'fname': 'Jisha', 'lname': 'Carpenter', 'hours': '25'}
  ]}, 
  {'Pname': 'ProductY', 'Pnumber': 2, 'Dname': 'Research', 'Employee': [
    {'fname': 'John', 'lname': 'Smith', 'hours': '7.5'}, 
    {'fname': 'Joyce', 'lname': 'English', 'hours': '20'}
  ]}
]

推荐阅读