JSON construction over multiple functions in Oracle SQL doesn't end in a good format

Question

I'm trying to create a JSON file over multiples functions inside a package in Oracle SQL. I can create the whole JSON in one single select, but it is a quite big select, so, I want to divide everything into multiple functions.

The problem is, when trying to assemble the whole JSON, the values that should have an array inside and maintain the JSON structure, are instead placed as a string.

small example:

"indicadoresOperacao": "[{\"id\":\"11122/2024\",\"indicadorConceitoSincronizado\":\"true\",\"indicadorSincronizarConceito\":\"false\"

this is generated here:

 KEY 'indicadoresOperacao'   VALUE get_json_indicadores_op_plano( id_pr),

and inside the function I have a SELECT creating a JSON_ARRAYAGG and I'm returning a CLOB.

everything that I tried that isn't creating the whole thing together (which takes more time) ends up in this same format, I think I know that the problem is me trying to associate the VALUE to a CLOB, instead of the select it self, and I don't know how to work around it to make the code easy to read and change when needed.

Answer 1

If you are returning values that are in a JSON format then use FORMAT JSON to use the value as-is:

KEY 'indicadoresOperacao'
  VALUE get_json_indicadores_op_plano(id_pr) FORMAT JSON

---

However, you may not need to use functions. For example, if you have the sample data:

CREATE TABLE table_name (id, ics, isc) AS
  SELECT '11122/2024', 1, 0 FROM DUAL UNION ALL
  SELECT '11123/2024', 0, 1 FROM DUAL;

Then you can represent it as an object containing an array of objects using:

SELECT JSON_OBJECT(
         KEY 'indicadoresOperacao' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'id' VALUE id,
             KEY 'indicadorConceitoSincronizado'
               VALUE CASE ics WHEN 1 THEN 'true' ELSE 'false' END FORMAT JSON,
             KEY 'indicadorSincronizarConceito'
               VALUE CASE isc WHEN 1 THEN 'true' ELSE 'false' END FORMAT JSON
             RETURNING CLOB
           )
           RETURNING CLOB
         )
         RETURNING CLOB
       ) AS json
FROM   table_name

Which outputs:

JSON
{"indicadoresOperacao":[{"id":"11122/2024","indicadorConceitoSincronizado":true,"indicadorSincronizarConceito":false},{"id":"11123/2024","indicadorConceitoSincronizado":false,"indicadorSincronizarConceito":true}]}

fiddle