SQLite使用JSON_ARRAYAGG聚合数据格式问题求助

完全不用换数据库，解决方法分两种情况，取决于你使用的SQLite版本：

情况1：SQLite版本 ≥ 3.38.0 #

这个版本开始原生支持JSON_ARRAYAGG函数，直接结合JSON_OBJECT就能生成嵌套的JSON数组结构。

假设你有categories（分类表）和products（产品表），关联字段为category_id，查询语句如下：

SELECT
  c.id AS category_id,
  c.name AS category_name,
  -- 用JSON_OBJECT把单个产品转为JSON对象，再用JSON_ARRAYAGG聚合为数组
  IFNULL(
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'product_id', p.id,
        'product_name', p.name,
        'price', p.price
        -- 按需添加其他产品字段
      )
    ),
    '[]' -- 处理无关联产品的分类，返回空数组而非NULL
  ) AS products
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name;

执行后products字段会是标准的JSON数组，每个元素是完整的产品对象，而非字符串集合。

情况2：SQLite版本 < 3.38.0 #

低版本没有JSON_ARRAYAGG，但可以用json_group_array替代，核心是先把单个产品转成JSON对象，再进行聚合——你之前用json_group_array没得到预期格式，大概率是直接聚合了p.name这类字段值，导致生成的是["产品A", "产品B"]这种字符串数组，而非对象数组。

示例语句：

SELECT
  c.id AS category_id,
  c.name AS category_name,
  IFNULL(
    json_group_array(
      json_object(
        'product_id', p.id,
        'product_name', p.name,
        'price', p.price
      )
    ),
    '[]'
  ) AS products
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name;

版本检查 #

不确定自己的SQLite版本？执行这条语句即可查看：

SELECT sqlite_version();

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