Hi.
I am facing a weird result from SQL/JSON using JSON_ARRAYAGG. I have found this situation on "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" but I have reproduced same results in LiveSQL with "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production".
I have created a table with 2 columns and populated with 3 rows:
CREATE table items
(
item_id NUMBER(5) not null,
item VARCHAR2(6) not null
)
;
insert into items (item_id, item)
values (1, '397850')
;
insert into items (item_id, item)
values (2, '397855')
;
insert into items (item_id, item)
values (3, '397855')
;
Item_id has 3 different values, but item only have 2 unique values. If I look for different values of item I only find 2 values:
SELECT DISTINCT item
FROM items;
If I convert items into JSON objects I have 2 rows/values:
SELECT JSON_OBJECT(
'item_obj' IS JSON_OBJECT(
'item' IS item))
AS qry_result
FROM (
SELECT DISTINCT item
FROM items
);
| QRY_RESULT |
|---|
| {"item_obj" : {"item" : "397855"}} |
| {"item_obj" : {"item" : "397850"}} |
But when I try to aggregate this objects as an JSON array I find this:
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'item_obj' IS JSON_OBJECT(
'item' IS item))
FORMAT JSON
ORDER BY item
RETURNING CLOB
)
AS qry_result
FROM (
SELECT DISTINCT item
FROM items
) ;
| QRY_RESULT |
|---|
[ {"item_obj" : {"item" : "397850"}}, {"item_obj" : {"item" : "397855"}}, {"item_obj" : {"item" : "397855"}} ] |
Can someone explain me why this query is returning 3 JSON objects instead of 2? Have I misunderstood something about JSON_ARRAYAGG? Is this a bug?
Regards.