Is there a way to add/append a value to JSON Array in Oracle 19?

Question

Is there an Oracle SQL (Oracle 19c) approach to add/append a value to a JSON array within an SQL query?

For e.g. if I have

with test_json as (
    select 123 as col1, json_array(1, 2, 3) as col2 from dual
)select * from test_json;

I want to add the value '100' to the array in col2

I have tried this

with test_json as (
    select 123 as col1, json_array(1, 2, 3) as col2 from dual
), test_json_append as (
    select col1, json_array(col2, 100) from test_json
)select * from test_json_append;

But I get a nested array which is not what I need.

Playing with JSON functions in Oracle, I made this

with test_json as (
select 123 as col1, json_array(1, 2, 3) as col2 from dual
)select json_arrayagg(x.col2)
    from (select a.col2 from test_json o, json_table (col2, '$[*]' columns(col2 number path '$')) a
            union
          select 100 as col2 from dual) x;

But, it is complicated for just appending a value to an array, and I don't think I can use it with other columns to be projected out.

PS: I am not looking for a PL/SQL solution. Interested to understand whether the Oracle JSON functions can do this in an SQL query.

Answer 1

You can create the function:

CREATE FUNCTION JSON_ARRAY_APPEND(
  array IN CLOB,
  value IN NUMBER
) RETURN CLOB
IS
  j_arr JSON_ARRAY_T  := JSON_ARRAY_T(array);
BEGIN
  j_arr.append( value );
  RETURN j_arr.to_Clob();
END;
/

Then:

WITH test_json (col1, col2) as (
  select 123, json_array(1, 2, 3) from dual
)
select col1,
       JSON_ARRAY_APPEND(col2, 100)
from   test_json;

Outputs:

COL1	JSON_ARRAY_APPEND(COL2,100)
123	[1,2,3,100]

---

Or, you can define the function in the query:

WITH FUNCTION JSON_ARRAY_APPEND(
  array IN CLOB,
  value IN NUMBER
) RETURN CLOB
IS
  j_arr JSON_ARRAY_T  := JSON_ARRAY_T(array);
BEGIN
  j_arr.append( value );
  RETURN j_arr.to_Clob();
END;
test_json (col1, col2) as (
    select 123, json_array(1, 2, 3) from dual
)
select col1,
       JSON_ARRAY_APPEND(col2, 100)
from   test_json;

---

Or, if you want to just use existing SQL functions, then you can split the array into rows, add another value and re-aggregate:

WITH test_json (col1, col2) as (
    select 123, json_array(1, 2, 3) from dual
)
select *
from   test_json t
       CROSS JOIN LATERAL(
         SELECT JSON_ARRAYAGG(value) AS col3
         FROM   (
           SELECT value
           FROM   JSON_TABLE( t.col2, '$[*]' COLUMNS value NUMBER PATH '$')
           UNION ALL
           SELECT 100 FROM DUAL
         )
       );

Which outputs:

COL1	COL2	COL3
123	[1,2,3]	[1,2,3,100]

db<>fiddle here