使用json_object的Mysql JSON输出
使用json_object的Mysql JSON输出
提问于 2021-09-07 05:53:15
我已经研究了好几天了，就是不能得到拼图的最后一块工作……
问题似乎是得到一个嵌套对象，其中2个嵌套对象在同一级别？
我需要获得如下输出：
{
"Id": "19115",
"ClassScheduleId": "2143",
"MaxCapacity": "30",
"WebCapacity": "20",
"TotalBooked": "0",
"TotalBookedWaitlist": "0",
"WebBooked": "0",
"SemesterId": null,
"IsCanceled": "0",
"Substitute": "0",
"Active": "1",
"IsWaitlistAvailable": "0",
"IsEnrolled": "0",
"HideCancel": "0",
"IsAvailable": "0",
"StartDateTime": "2021-09-06T09:15:00+02:00",
"EndDateTime": "2021-09-06T10:15:00+02:00",
"LastModifiedDateTime": "2021-09-07T02:35:21+02:00",
"StaffId": "100000237",
"BookingStatus": "PaymentRequired",
"VirtualStreamLink": null,
"ClassDescription": {
"Id": "257",
"Active": "0",
"Description": "<div>Designed to burn calories while toning and lengthening, this challenging class mixes intense, high-impact cardio and plyometric training with full body conditioning and yoga. Using popular and motivating music, this fast-paced, high energy class will help create that long and lean look. No class is ever the same to ensure progressive results</div>",
"LastUpdated": "2021-08-17T15:31:21+02:00",
"Name": "Bottoms Up",
"Notes": "",
"Prereq": "",
"Program": {
"Id": "26",
"Name": "Classes",
"ScheduleType": "Class",
"CancelOffset": "0"
},
"SessionType": {
"Id": "207",
"Type": "All",
"Name": "Spinning",
"NumDeducted": "1",
"ProgramId": "26"
}
}
}我的最后一次尝试，我认为是“关闭”，只是没有工作，并抛出一个错误:-(
SELECT json_arrayagg(
json_object(
'ClassScheduleId', c.ClassScheduleId,
'MaxCapacity', c.MaxCapacity,
'WebCapacity', c.WebCapacity,
'TotalBooked', c.TotalBooked,
'TotalBookedWaitlist', c.TotalBookedWaitlist,
'WebBooked', c.WebBooked,
'IsCanceled', c.IsCanceled,
'Substitute', c.Substitute,
'Active', c.Active,
'IsWaitlistAvailable', c.IsWaitlistAvailable,
'IsEnrolled', c.IsEnrolled,
'HideCancel', c.HideCancel,
'Id', c.Id,
'IsAvailable', c.IsAvailable,
'StartDateTime', c.StartDateTime,
'EndDateTime', c.EndDateTime,
'LastModifiedDateTime', c.LastModifiedDateTime,
'BookingStatus', c.BookingStatus
'ClassDescription', json_object(
'Active', cd.Active,
'Description', cd.Description,
'Id', cd.Id,
'LastUpdated', cd.LastUpdated,
'Name', cd.Name,
'Notes', cd.Notes,
'Prereq', cd.Prereq,
GROUP_CONCAT(
'Program', json_object(
'Id', p.Id,
'Name', p.Name,
'ScheduleType', p.ScheduleType,
'CancelOffset', p.CancelOffset
),
'SessionType', json_object(
'Type', st.Type,
'Id', st.Id,
'Name', st.Name,
'NumDeducted', st.NumDeducted,
'ProgramId', st.ProgramId
)
)
)
)
)
FROM Classes as c
LEFT JOIN ClassDescription as cd ON cd.Id = c.ClassDescriptionId
LEFT JOIN Program as p ON p.Id = cd.ProgramId
LEFT JOIN SessionType as st ON st.Id = cd.SessionTypeId有什么想法可以让我获得更好的文档或SQL可视化编辑器，能够格式化/构建这些类型的查询？
提前感谢您的任何意见！
回答 1
Stack Overflow用户
发布于 2021-09-07 07:50:42
答对了！魔鬼总是在细节中
似乎我错过了，在'BookingStatus‘之后，c.BookingStatus和我也删除了GROUP_CONCAT！
这工作得很完美！
SELECT json_arrayagg(
json_object(
'ClassScheduleId', c.ClassScheduleId,
'MaxCapacity', c.MaxCapacity,
'WebCapacity', c.WebCapacity,
'TotalBooked', c.TotalBooked,
'TotalBookedWaitlist', c.TotalBookedWaitlist,
'WebBooked', c.WebBooked,
'IsCanceled', c.IsCanceled,
'Substitute', c.Substitute,
'Active', c.Active,
'IsWaitlistAvailable', c.IsWaitlistAvailable,
'IsEnrolled', c.IsEnrolled,
'HideCancel', c.HideCancel,
'Id', c.Id,
'IsAvailable', c.IsAvailable,
'StartDateTime', c.StartDateTime,
'EndDateTime', c.EndDateTime,
'LastModifiedDateTime', c.LastModifiedDateTime,
'BookingStatus', c.BookingStatus,
'ClassDescription', json_object(
'Active', cd.Active,
'Description', cd.Description,
'Id', cd.Id,
'LastUpdated', cd.LastUpdated,
'Name', cd.Name,
'Notes', cd.Notes,
'Prereq', cd.Prereq,
'Program', json_object(
'Id', p.Id,
'Name', p.Name,
'ScheduleType', p.ScheduleType,
'CancelOffset', p.CancelOffset
),
'SessionType', json_object(
'Type', st.Type,
'Id', st.Id,
'Name', st.Name,
'NumDeducted', st.NumDeducted,
'ProgramId', st.ProgramId
)
)
)
)
FROM Classes as c
LEFT JOIN ClassDescription as cd ON cd.Id = c.ClassDescriptionId
LEFT JOIN Program as p ON p.Id = cd.ProgramId
LEFT JOIN SessionType as st ON st.Id = cd.SessionTypeId页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69083006
复制相关文章
相似问题
腾讯云开发者
