MySQL使用GROUP_CONCAT拼接DISTINCT JSON_OBJECT仅保留available=true条目问题

问题原因 #

你当前写法的问题在于：多表JOIN后同一个modifiers_items条目会匹配到多条product_modifiers记录，生成JSON_OBJECT时同一个item id会同时生成available=true和available=false两个不同的JSON对象，GROUP_CONCAT的DISTINCT无法合并两个字段值不同的JSON，你直接在JSON_OBJECT内部写MAX(IF(...))也不会生效，因为聚合函数执行晚于JSON对象生成，也不会自动按item id去重取最优值。

修改后的完整代码 #

SELECT 
  modifiers.id,
  modifiers.name_ar,
  modifiers.name_en,
  CONCAT("[", GROUP_CONCAT(
    JSON_OBJECT(
      "id", item_id,
      "name_ar", item_name_ar,
      "name_en", item_name_en,
      "available", available
    )
  ), "]") AS items 
FROM `modifiers` 
LEFT JOIN (
  -- 先预聚合每个modifier下的条目，计算出最终的available状态
  SELECT
    mi.modifier_id,
    mi.id AS item_id,
    mi.name_ar AS item_name_ar,
    mi.name_en AS item_name_en,
    MAX(IF(pm.modifiers_item_id = mi.id, "true", "false")) AS available
  FROM modifiers_items mi
  LEFT JOIN product_modifiers pm 
    ON pm.modifiers_id = mi.modifier_id 
    AND pm.product_id = '1'
  GROUP BY mi.modifier_id, mi.id
) AS item_final_status ON item_final_status.modifier_id = modifiers.id
WHERE modifiers.status <> '2'
GROUP BY modifiers.id

改动逻辑说明 #

• 新增子查询先按modifier_id和modifiers_items.id分组，提前为每个条目算出最终的available状态：只要存在匹配的product_modifiers记录就返回true，否则返回false，保证每个item只有唯一一条记录
• 把product_id = '1'的过滤条件移到子查询的JOIN条件中，避免过滤掉没有绑定当前商品的modifier记录
• 外层GROUP_CONCAT不需要再加DISTINCT，子查询已经保证了条目的唯一性，返回结构和你原有逻辑完全一致

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