Hi.
I am using "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0" with database charater set AL32UTF8. I am receiving a wrong (or incomplete) response from JSON_ARRAYAGG when strings have special characters as á,é,í,ó,ú,ñ.
Test case:
create table ANIMALS
(
animal_id NUMBER(2) not null,
aname VARCHAR2(20 CHAR) not null
)
insert into ANIMALS (ANIMAL_ID, ANAME)
values (1, 'gnu');
insert into ANIMALS (ANIMAL_ID, ANAME)
values (2, 'pig');
insert into ANIMALS (ANIMAL_ID, ANAME)
values (3, 'león');
With query:
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'animal_id' IS animal_id,
'aname' IS aname)
ORDER BY aname
RETURNING CLOB
) AS animals
FROM (SELECT animal_id,
aname
FROM animals
WHERE animal_id < 3
);
It returns a CLOB with content:
[{"animal_id":1,"aname":"gnu"},{"animal_id":2,"aname":"pig"}]
But If the query contains row with 'león':
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'animal_id' IS animal_id,
'aname' IS aname)
ORDER BY aname
RETURNING CLOB
) AS animals
FROM (SELECT animal_id,
aname
FROM animals
WHERE animal_id <= 3
);
It returns a CLOB that is not a valid JSON (incomplete):
[{"animal_id":1,"aname":"gnu"},{"animal_id":3,"aname":"león"}
The final square bracket is missing.
If I don´t use "RETURNING CLOB" the results are right:
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'animal_id' IS animal_id,
'aname' IS aname)
ORDER BY aname
) AS animals
FROM (SELECT animal_id,
aname
FROM animals
WHERE animal_id <= 3
);
It returns a string that is a valid JSON:
[{"animal_id":1,"aname":"gnu"},{"animal_id":3,"aname":"león"},{"animal_id":2,"aname":"pig"}]
I suppose this is a bug. I have tested same queries in LiveSQL and they are working fine in Oracle 18c.
In other cases I have observed that the final brace of the object with special characters is being duplicated.
Is there any patch to solve this?
Regards.