JSON_ARRAYAGG returns a truncated list of data

Question

I am trying to create a JSON output but it is displayed truncated.

Table creation and inserts:

create table test(c1 number, c2 varchar2(10), c3 varchar2(100));

begin
  for i in 1 .. 1000 loop
    insert into test values (i, 'val_' || i, 'descption of ' || i);
  end loop;
end;

SQL query I execute:

SELECT JSON_ARRAYAGG(
           distinct JSON_OBJECT(
                   'id' VALUE c1,
                   'value' VALUE c2,
                   'description'  VALUE c3) RETURNING CLOB)
  FROM test;

Here it is the output:

[{"id":1,"value":"val_1","description":"descption of 1"

I am using Oracle 12.2.0.1.0 database version and SQL Developer Version 23.1.1.339, Build 339.1213

Can anyone help me to understand what should I do to get all the rows, please?

Thank you,

Answer 1

You could try to test it like below.

create table TEST_TABLE(c1 number, c2 varchar2(10), c3 varchar2(100));
begin
  for i in 1 .. 1000 loop
    insert into TEST_TABLE values (i, 'val_' || i, 'descption of ' || i);
  end loop;
end;
/
Commit;

SELECT id, value, description
FROM json_table(  ( SELECT J_ARR
                    FROM (Select J_ARR
                          From  ( SELECT JSON_ARRAYAGG(
                                         distinct
                                         JSON_OBJECT(
                                           'id'          VALUE c1,
                                           'value'       VALUE c2,
                                           'description' VALUE c3
                                         )
                                         RETURNING CLOB
                                      ) "J_ARR"
                                  FROM  TEST_TABLE
                                ) 
                         )
                    ) , '$[*]'
                    COLUMNS ( id NUMBER PATH '$.id',
                              value VARCHAR PATH '$.value',
                              description VARCHAR PATH '$.description'
                            )
               ) 
/*    R e s u l t :
ID   VALUE    DESCRIPTION
---- -------- -------------
   1 val_1    descption of 1
   2 val_2    descption of 2
   3 val_3    descption of 3
... ... ...
 999 val_999    descption of 999
1000 val_1000   descption of 1000  */

If this test gives the same result in your SQLDeveloper then it is a fact that result of your code from the question IS A PROPPER AND COMPLETE JSON ARRAY OF THREE FIELDS. Why you don't see that in your SQLDeveloper's Query Result Grid (not in Script Output which will truncate it) is impossible to say. The thing is that if the result of your JSON ARRAY generation was in any way false this test will end in error.
"Can anyone help me to understand what should I do to get all the rows, please?"
To make it short: If the test works then you should do nothing - you already have all the rows.
Addition:
Run command from picture to see the length of your array and do it as in the picture:

Alternative is :

1. click the cell (it should get blue background)
2. Ctrl + C (copy)
3. Open any text editor and Ctrl + V (paste)

OR

1. doubleclick the cell (background stays white)
2. Ctrl + A (select all) (background changes to blue)
3. Ctrl + C (copy)
4. Open any text editor and Ctrl + V (paste)

Answer 2

After some tests, it seems I solved the problem by an workaround.

The workaround consist in changing the data type from RETURNING clause from CLOB to VARCHAR2(32000) and using a WHERE clause to reduce the number of rows displayed:

SELECT JSON_ARRAYAGG(
           distinct JSON_OBJECT(
                   'id' VALUE c1,
                   'value' VALUE c2,
                   'description'  VALUE c3) RETURNING VARCHAR2(32000))
  FROM test
WHERE ROWNUM < 500;

The WHERE clause can contain any condition that will reduce the number of rows to be diplayed.