使用MySQL的json_objectagg时如何保留JSON数组？

问题核心在于CASE语句的两个分支返回类型不统一：当count(v) > 1时返回JSON数组类型，ELSE分支返回普通数值/字符串类型。数据库为了统一列类型，会自动将JSON数组转换为字符串，最终导致外层json_objectagg生成的JSON中，数组被当作字符串值包裹在引号里。

修复方案 #

让CASE语句的两个分支都返回JSON类型，将ELSE分支的v转换为JSON标量即可。以下是适配不同数据库的修改方案：

MySQL环境

修改内层查询的CASE部分：

select
    rd.r,
    json_objectagg(rd.f, rd.r_data) as rdata
from (
  select
    r,
    f,
    -- 将ELSE分支的v转为JSON类型，确保CASE返回类型统一
    CASE WHEN (count(v) > 1) THEN json_arrayagg(v) ELSE CAST(v AS JSON) END as r_data
  from (
    select 1 as r, 1 as f, 1 as v
    union all
    select 1 as r, 2 as f, 'a string' as v
    union all
    select 1 as r, 2 as f, 3 as v
    union all
    select 2 as r, 1 as f, 1 as v
    union all
    select 2 as r, 2 as f, 2 as v
    union all
    select 2 as r, 2 as f, '2023-01-01' as v
    union all
    select 3 as r, 1 as f, 1 as v
    union all
    select 3 as r, 2 as f, 2 as v
    union all
    select 3 as r, 2 as f, 'true' as v
  ) row_data
    group by row_data.r, row_data.f
  order by row_data.r, row_data.f
) rd
GROUP BY rd.r

PostgreSQL环境

PostgreSQL需调整聚合函数和类型转换语法：

select
    rd.r,
    json_object_agg(rd.f, rd.r_data) as rdata
from (
  select
    r,
    f,
    CASE WHEN (count(v) > 1) THEN json_agg(v) ELSE to_json(v) END as r_data
  from (
    select 1 as r, 1 as f, 1 as v
    union all
    select 1 as r, 2 as f, 'a string' as v
    union all
    select 1 as r, 2 as f, 3 as v
    union all
    select 2 as r, 1 as f, 1 as v
    union all
    select 2 as r, 2 as f, 2 as v
    union all
    select 2 as r, 2 as f, '2023-01-01' as v
    union all
    select 3 as r, 1 as f, 1 as v
    union all
    select 3 as r, 2 as f, 2 as v
    union all
    select 3 as r, 2 as f, 'true' as v
  ) row_data
    group by row_data.r, row_data.f
  order by row_data.r, row_data.f
) rd
GROUP BY rd.r

效果说明 #

修改后r_data列的所有值均为JSON类型，外层聚合函数会正确识别JSON数组，最终生成的JSON结构为：

{"1": 1, "2": ["a string", 3]}

而非数组被转为字符串的错误格式。

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