MySQL SELECT语句返回嵌套JSON对象格式修正需求

问题描述 #

在MySQL的SELECT语句中使用json_object()和json_arrayagg()生成JSON对象时，json_arrayagg()的结果会变成带转义字符的字符串，而非原生JSON数组；同时没有所有者的资产，owners字段返回null，期望改为空数组[]。要求仅修改MySQL语句，不依赖后端代码处理。

当前输出 #

{title: "MY_ASSET", id: "a_1", owners: "[{\"id\": \"u_1\", \"username\": \"john\"}]"},
{title: "OTHER_ASSET", id: "a_2", owners: null}

期望输出 #

{title: "MY_ASSET", id: "a_1", owners: [{"id": "u_1", "username": "john"}]},
{title: "OTHER_ASSET", id: "a_2", owners: []}

解决方案 #

直接在SQL中通过外层JSON_OBJECT()包裹所有字段，并结合COALESCE()处理空值，确保输出为原生JSON格式：

修改后的SQL语句 #

SELECT
  JSON_OBJECT(
    'id', a.id,
    'title', a.title,
    'owners', COALESCE(a_owners.owners, JSON_ARRAY())
  ) AS asset_json
FROM assets AS a
LEFT JOIN (
  SELECT
    a.id,
    JSON_ARRAYAGG(JSON_OBJECT(
      'id', ua.user_id,
      'username', u.username
    )) AS owners
  FROM assets AS a
  INNER JOIN user_assets AS ua ON a.id = ua.asset_id
  LEFT JOIN `users` AS u ON ua.user_id = u.id
  WHERE ua.user_role = 'owner'
  GROUP BY a.id
) AS a_owners ON a_owners.id = a.id;

关键修改点 #

• 外层用JSON_OBJECT整合：把子查询返回的owners数组直接嵌入到外层的JSON_OBJECT中，避免数组被当作字符串处理，从而消除转义字符。
• COALESCE处理空值：用COALESCE(a_owners.owners, JSON_ARRAY())将无所有者时的null替换为空数组[]，符合期望输出格式。

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