使用PHP从MySQL一对多关系中获取嵌套JSON数据的实现问题

首先你原有查询存在两处语法错误：

1. FROM语句末尾多余了分号，会导致WHERE条件被识别为独立语句触发报错
2. 关联字段匹配错误，两张表的关联主键均为user_id，不是你写的users.id和subjects.id

方案1：低版本MySQL兼容方案（5.7以下） #

先用GROUP_CONCAT聚合科目字段，查询后在应用层拆分转换为数组即可：

SELECT 
  u.user_id,
  u.name,
  GROUP_CONCAT(s.subject SEPARATOR ',') AS subject_list
FROM `user` u
INNER JOIN `subject` s 
ON u.user_id = s.user_id
GROUP BY u.user_id, u.name

你拿到查询结果后，把subject_list按逗号拆分，单元素直接取字符串、多元素存为数组，就能完全匹配你要的输出格式。

方案2：MySQL 5.7.22+ 直接生成目标JSON #

用MySQL原生JSON函数可以一步直接输出你需要的嵌套结构：

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'user_id', CAST(u.user_id AS CHAR),
    'name', u.name,
    'subject', IF(COUNT(s.subject) = 1, MIN(s.subject), JSON_ARRAYAGG(s.subject))
  )
) AS target_json
FROM `user` u
INNER JOIN `subject` s 
ON u.user_id = s.user_id
GROUP BY u.user_id, u.name

这里加了CAST(u.user_id AS CHAR)是为了匹配你示例里user_id为字符串的格式，如果不需要可以删掉直接用u.user_id；如果你可以接受单科目也存为数组格式，把IF判断去掉直接写'subject', JSON_ARRAYAGG(s.subject)即可。

内容的提问来源于stack exchange，提问作者Zia Ur Rahman