MySQL使用json_object生成同级嵌套JSON对象的实现问题求助

错误点说明 #

• 语法错误：'BookingStatus', c.BookingStatus 末尾缺少逗号，导致JSON对象键值对解析失败
• 逻辑错误：错误使用GROUP_CONCAT包裹Program和SessionType两个对象，这两个是ClassDescription下的同级键值对，不需要字符串拼接，直接作为json_object的参数即可
• 字段缺失：缺少目标JSON中要求的StaffId、SemesterId、VirtualStreamLink等字段，需要补全

修正后的SQL代码 #

SELECT json_arrayagg(
            json_object(
                'Id', c.Id,
                'ClassScheduleId', c.ClassScheduleId,
                'MaxCapacity', c.MaxCapacity,
                'WebCapacity', c.WebCapacity,
                'TotalBooked', c.TotalBooked,
                'TotalBookedWaitlist', c.TotalBookedWaitlist,
                'WebBooked', c.WebBooked,
                'SemesterId', NULL,
                'IsCanceled', c.IsCanceled,
                'Substitute', c.Substitute,
                'Active', c.Active,
                'IsWaitlistAvailable', c.IsWaitlistAvailable,
                'IsEnrolled', c.IsEnrolled,
                'HideCancel', c.HideCancel,
                'IsAvailable', c.IsAvailable,
                'StartDateTime', c.StartDateTime,
                'EndDateTime', c.EndDateTime,
                'LastModifiedDateTime', c.LastModifiedDateTime,
                'StaffId', c.StaffId,
                'BookingStatus', c.BookingStatus,
                'VirtualStreamLink', NULL,
                'ClassDescription', json_object(
                    'Id', cd.Id,
                    'Active', cd.Active,
                    'Description', cd.Description,
                    '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(
                        'Id', st.Id,
                        'Type', st.Type,
                        '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

文档与工具推荐 #

• 文档：不同数据库的JSON函数规则存在差异，直接查询对应数据库的官方文档中JSON函数章节即可，内容最全面准确
• 可视化编辑器：DataGrip、Navicat Premium、DBeaver社区版都支持JSON函数语法提示、SQL格式化，部分工具还支持JSON结果预览，可辅助编写这类嵌套JSON查询

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