SQL中能否在一个GROUP_CONCAT内部嵌套使用另一个GROUP_CONCAT？

需要实现三层关联数据的JSON聚合输出：顶层为POI记录，每个POI关联多条STORY记录，每条STORY又关联多条CONTENT记录，要求最终返回行数与POI总数一致，每行嵌套当前POI下所有STORY、每个STORY下所有CONTENT的JSON结构，方便后续解析还原业务对象。
最初编写的SQL如下：

select
  p.identifier,
  GROUP_CONCAT(
    '[' ||
    '{"thumbnail":' ||
    '"' ||
    ifnull(s.thumbnail,'null') ||
    '"' ||
    ',"title:' ||
    '"' ||
    s.title ||
    '","content": [' ||
    GROUP_CONCAT(
      '{"text":' ||
      ifnull(c.text,'null') ||
      '", "image":' || 
      ifnull(c.image,'null') ||
      '", "caption": "' ||
      ifnull(c.caption,'null') ||
      '"},'
    ) ||
    ']},'
  )
from pois as p
join  stories as s on p.identifier = s.poiid
join content c on s.storyid = c.storyid
group by s.storyid 

执行时抛出报错：

in prepare, misuse of aggregate function GROUP_CONCAT()

• 你使用的SQLite引擎不支持直接嵌套聚合函数：原SQL直接在外层GROUP_CONCAT里嵌套内层GROUP_CONCAT，没有明确两层聚合的分组维度，引擎无法执行，直接抛出聚合函数误用的错误。
• 分组逻辑不符合需求：你需要最终返回行数和POI总数一致，但原SQL写的是group by s.storyid，返回结果会按STORY维度聚合，行数和STORY数对齐，达不到预期。
• 手动拼接JSON存在多处语法缺陷：比如title键名漏写闭合双引号、字符串值的引号位置错位、数组拼接完成后末尾会多出多余逗号，就算语法不报错，生成的JSON也无法正常解析。

采用分层聚合的思路，先完成最内层CONTENT按STORY维度的聚合，再基于中间结果完成STORY按POI维度的聚合，从根源上避免嵌套聚合问题。优先使用数据库内置JSON函数生成结构，避免手动拼接的格式错误。

方案1：SQLite 3.38.0及以上版本（支持内置JSON函数，推荐） #

用官方json_group_array、json_object函数自动生成合法JSON，无需手动处理引号、多余逗号、特殊字符转义问题：

WITH story_agg AS (
  -- 第一步：按story维度聚合，将每条story下的所有content聚合成JSON数组
  SELECT
    s.poiid,
    s.thumbnail,
    s.title,
    json_group_array(
      json_object(
        'text', c.text,
        'image', c.image,
        'caption', c.caption
      )
    ) AS content_list
  FROM stories s
  JOIN content c ON s.storyid = c.storyid
  GROUP BY s.storyid
)
-- 第二步：按poi维度聚合，将每个poi下的所有story聚合成JSON数组
SELECT
  p.identifier,
  json_group_array(
    json_object(
      'thumbnail', sa.thumbnail,
      'title', sa.title,
      'content', sa.content_list
    )
  ) AS story_list
FROM pois p
JOIN story_agg sa ON p.identifier = sa.poiid
GROUP BY p.identifier;

方案2：低版本SQLite无内置JSON函数（手动拼接） #

如果版本不支持JSON函数，分层聚合后手动拼接，注意通过RTRIM去掉数组末尾多余逗号：

WITH story_agg AS (
  SELECT
    s.poiid,
    s.thumbnail,
    s.title,
    -- 内层聚合content，移除末尾多余逗号
    '[' || RTRIM(
      GROUP_CONCAT(
        '{"text":"' || IFNULL(c.text, 'null') ||
        '","image":"' || IFNULL(c.image, 'null') ||
        '","caption":"' || IFNULL(c.caption, 'null') || '"}'
      ), ','
    ) || ']' AS content_list
  FROM stories s
  JOIN content c ON s.storyid = c.storyid
  GROUP BY s.storyid
)
SELECT
  p.identifier,
  -- 外层聚合story，移除末尾多余逗号
  '[' || RTRIM(
    GROUP_CONCAT(
      '{"thumbnail":"' || IFNULL(sa.thumbnail, 'null') ||
      '","title":"' || IFNULL(sa.title, 'null') ||
      '","content":' || sa.content_list || '}'
    ), ','
  ) || ']' AS story_list
FROM pois p
JOIN story_agg sa ON p.identifier = sa.poiid
GROUP BY p.identifier;

• 如果存在POI未关联STORY、STORY未关联CONTENT的场景，将SQL里的JOIN替换为LEFT JOIN即可保留空记录，避免数据丢失。
• 手动拼接JSON时如果字段值包含双引号、斜杠、换行符等特殊字符，需要额外做转义处理，否则会导致JSON格式损坏，生产环境优先选择内置JSON函数方案，稳定性更高。

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