PLSQL : for loop json_arrayagg to json

Question

I have an emp table, i.e.

insert into emp(id,name,salary) values (1,'name1',1000);
insert into emp(id,name,salary) values (2,'name2',2000);
insert into emp(id,name,salary) values (3,'name3',3000);
insert into emp(id,name,salary) values (4,'name4',4000);
insert into emp(id,name,salary) values (5,'name5',5000);

And I would like to have a query to output as an array of json object with additional language field.

After many retries, the closest I can get is with below:

declare 
    type v_lang_type is table of varchar2(20); 
    v_langs v_lang_type; 
    result clob; 
begin 
  v_langs := v_lang_type('EN','DE','SG','ES');   
FOR I IN v_langs.first .. v_langs.LAST 
  LOOP 
select json_arrayagg( 
    json_object( 
    'id' is id, 
    'name' is name, 
    'salary' is salary, 
    'lang' value v_langs(I) 
) format json returning clob) into result from emp; 
 dbms_output.put_line( result );
END LOOP; 
 
end;

Below is the result so far.

[{"id":1,"name":"name1","salary":1000,"lang":"EN"},{"id":2,"name":"name2","salary":2000,"lang":"EN"},{"id":3,"name":"name3","salary":3000,"lang":"EN"},{"id":4,"name":"name4","salary":4000,"lang":"EN"},{"id":5,"name":"name5","salary":5000,"lang":"EN"}]
[{"id":1,"name":"name1","salary":1000,"lang":"DE"},{"id":2,"name":"name2","salary":2000,"lang":"DE"},{"id":3,"name":"name3","salary":3000,"lang":"DE"},{"id":4,"name":"name4","salary":4000,"lang":"DE"},{"id":5,"name":"name5","salary":5000,"lang":"DE"}]
[{"id":1,"name":"name1","salary":1000,"lang":"SG"},{"id":2,"name":"name2","salary":2000,"lang":"SG"},{"id":3,"name":"name3","salary":3000,"lang":"SG"},{"id":4,"name":"name4","salary":4000,"lang":"SG"},{"id":5,"name":"name5","salary":5000,"lang":"SG"}]
[{"id":1,"name":"name1","salary":1000,"lang":"ES"},{"id":2,"name":"name2","salary":2000,"lang":"ES"},{"id":3,"name":"name3","salary":3000,"lang":"ES"},{"id":4,"name":"name4","salary":4000,"lang":"ES"},{"id":5,"name":"name5","salary":5000,"lang":"ES"}]

While the target I would like to achieve is this:

[
    {
        "id": 1,
        "language": "EN",
        "name": "name1",
        "salary": 1000
    },
    {
        "id": 2,
        "language": "EN",
        "name": "name2",
        "salary": 2000
    },
    {
        "id": 3,
        "language": "EN",
        "name": "name3",
        "salary": 3000
    },
    {
        "id": 1,
        "language": "DE",
        "name": "name1",
        "salary": 1000
    },
    {
        "id": 2,
        "language": "DE",
        "name": "name2",
        "salary": 2000
    },
    {
        "id": 3,
        "language": "DE",
        "name": "name3",
        "salary": 3000
    },
    {
        "id": 1,
        "language": "SG",
        "name": "name1",
        "salary": 1000
    },
    {
        "id": 2,
        "language": "SG",
        "name": "name2",
        "salary": 2000
    },
    {
        "id": 3,
        "language": "SG",
        "name": "name3",
        "salary": 3000
    },
    {
        "id": 1,
        "language": "ES",
        "name": "name1",
        "salary": 1000
    },
    {
        "id": 2,
        "language": "ES",
        "name": "name2",
        "salary": 2000
    },
    {
        "id": 3,
        "language": "ES",
        "name": "name3",
        "salary": 3000
    }
]

Any idea or pointer on how to achieve that?

score 0 · Answer 1 · answered May 03 '23 at 19:42

Does it need to be pl/sql ? In sql this is possible. JSON_ARRAYAGG generates a single array. By using CROSS JOIN and representing the language value as a table it's possible to generate the complete json object in a single select

create table emp2 (id,name,salary) AS
(
select 1, 'name1', 1000 from dual union all
select 2, 'name2', 2000 from dual union all
select 3, 'name3', 3000 from dual 
);

Table EMP2 created.

Elapsed: 00:00:00.300

SELECT JSON_ARRAYAGG (
        JSON_OBJECT(
        KEY 'id' VALUE e.id,
        KEY 'name' VALUE e.name,
        KEY 'salary' VALUE e.salary,
        KEY 'lang' VALUE l.lang
        )
    )
FROM   emp2 e CROSS JOIN 
(SELECT 'EN' AS lang from dual union all
SELECT 'DE' AS lang from dual union all
SELECT 'SG' AS lang from dual
) l;

[
  {
    "id": 1,
    "name": "name1",
    "salary": 1000,
    "lang": "EN"
  },
  {
    "id": 2,
    "name": "name2",
    "salary": 2000,
    "lang": "EN"
  },
  {
    "id": 3,
    "name": "name3",
    "salary": 3000,
    "lang": "EN"
  },
  {
    "id": 1,
    "name": "name1",
    "salary": 1000,
    "lang": "DE"
  },
  {
    "id": 2,
    "name": "name2",
    "salary": 2000,
    "lang": "DE"
  },
  {
    "id": 3,
    "name": "name3",
    "salary": 3000,
    "lang": "DE"
  },
  {
    "id": 1,
    "name": "name1",
    "salary": 1000,
    "lang": "SG"
  },
  {
    "id": 2,
    "name": "name2",
    "salary": 2000,
    "lang": "SG"
  },
  {
    "id": 3,
    "name": "name3",
    "salary": 3000,
    "lang": "SG"
  }
]

for pl/sql, well just use that same query.

PLSQL : for loop json_arrayagg to json

1 Answers1