ORA-40478: output value too large (maximum: 4000) while using JSON_ARRAYAGG

I am trying to get output of a query from a oracle database as JSON, through JSON_ARRAYAGG.

#   SELECT name AS name FROM admin_users
SELECT JSON_ARRAYAGG(name) AS name FROM admin_users

However, above gives me:

ORA-40478: output value too large (maximum: 4000)

Using RETURNING CLOB resolves the issue but it returns (<oracledb.LOB object at 0x0000021F5DA12C10>,) instead of JSON.

SELECT JSON_ARRAYAGG(name RETURNING CLOB) AS name FROM admin_users

Is there a way to fix the output value error without changing the return type?

JSON_ARRAYAGG will default to returning a VARCHAR2 and VARCHAR2 is limited to 4000 characters so you either:

1. Return a VARCHAR2 and if the output is over 4000 characters then you will get an exception; or
2. Use RETURNING CLOB which will allow you to have more than 4000 characters and then you read the value from the CLOB using:
   • clob_variable.read() or
   • Set oracledb.defaults.fetch_lobs = False so that "CLOBs and BLOBs smaller than 1 GB can queried from the database directly as strings and bytes."

As pointed out by @ChristopherJones in comments, there is a JSON example in the documentation.