如何通过PLSQL的FOR循环与JSON_ARRAYAGG生成合并的员工JSON数组

方法一：纯SQL实现（推荐） #

直接通过cross join实现员工记录与语言列表的笛卡尔积，再用json_arrayagg生成统一的JSON数组，同时指定格式化输出：

select json_arrayagg(
         json_object(
           'id' is e.id,
           'language' is l.lang,
           'name' is e.name,
           'salary' is e.salary
         )
         order by l.lang, e.id  -- 保证输出顺序与目标一致
         returning clob pretty  -- 格式化JSON输出
       ) as result
from emp e
cross join (
  -- 构造语言列表集合
  select column_value as lang
  from table(sys.odcivarchar2list('EN','DE','SG','ES'))
) l
where e.id <= 3;  -- 匹配目标输出中仅id1-3的记录，无需则删除此条件

方法二：修改原PL/SQL代码 #

如果必须用PL/SQL实现，需要先收集所有语言对应的JSON对象，再统一聚合成一个数组：

declare 
    type v_lang_type is table of varchar2(20); 
    v_langs v_lang_type; 
    type json_obj_table is table of json_object_t;
    v_json_objs json_obj_table := json_obj_table();
    result clob; 
begin 
  v_langs := v_lang_type('EN','DE','SG','ES');   
  
  -- 遍历所有语言，逐个生成对应员工的JSON对象并收集
  FOR I IN v_langs.first .. v_langs.LAST LOOP 
    for rec in (select id, name, salary from emp where id <=3) loop
      v_json_objs.extend();
      v_json_objs(v_json_objs.count) := json_object_t(
        json_object(
          'id' is rec.id,
          'language' is v_langs(I),
          'name' is rec.name,
          'salary' is rec.salary
        )
      );
    end loop;
  END LOOP; 
  
  -- 将所有对象聚合成单个JSON数组并输出
  result := json_arrayagg(v_json_objs) returning clob pretty;
  dbms_output.put_line(result);
end;
/

核心思路说明 #

1. 笛卡尔积关联：需要将每条员工记录与每个语言进行关联，生成多倍于原表的记录数，这是实现目标输出的核心
2. 字段名修正：将原代码中的lang字段名改为language，匹配目标输出要求
3. 过滤与排序：通过where e.id <=3过滤出目标中的员工记录，order by保证输出顺序与示例一致
4. 格式化输出：使用pretty参数让JSON结构更清晰，与目标格式对齐

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