Search notes:
Oracle: JSON_ARRAY
json_array turns a list of numbers, strings or JSON objects into a VARCHAR2 representation of a
JSON array.
By default,
null values are not included in the returned value:
select
json_array (42, 'Hello world', null, sysdate )
from
dual;
--
-- [42,"Hello world","2021-08-09T12:54:39"]
In order to include null values in the return value, NULL ON NULL needs to be included:
select
json_array (42, 'Hello world', null, sysdate NULL ON NULL)
from
dual;
--
-- [42,"Hello world",null,"2021-08-09T12:54:39"]
The following example returns a JSON array that contains three JSON objects:
select
json_array (
json_object ('id' value 1 ,'item' value 'foo' ,'pit' value sysdate + dbms_random.value*100),
json_object ('id' value 2 ,'item' value 'bar' ,'pit' value sysdate + dbms_random.value*100),
json_object ('id' value 3 ,'item' value 'baz' ,'pit' value sysdate + dbms_random.value*100)
)
from
dual;
--
-- [{"id":1,"item":"foo","pit":"2021-10-21T03:44:35"},{"id":2,"item":"bar","pit":"2021-08-11T20:27:02"},{"id":3,"item":"baz","pit":"2021-10-08T20:48:23"}]
Booleans can only be used in a
PL/SQL context:
declare
j clob;
begin
j := json_array(true, false);
dbms_output.put_line(j);
end;
/
--
-- [true,false]
Aggregating records into a single array
The following query returns four records, each of which is a JSON array with one element:
select
json_array(column_name)
from
dba_tab_cols
where
owner = 'SYS' and
table_name = 'USER_TAB_COMMENTS'
;
--
-- JSON_ARRAY(COLUMN_NAME)
-- ------------------------
-- ["TABLE_NAME"]
-- ["TABLE_TYPE"]
-- ["COMMENTS"]
-- ["ORIGIN_CON_ID"]
Using
json_arrayagg instead of
json_array flattens the result and returns
one array with
four elements:
select
json_arrayagg(column_name)
from
dba_tab_cols
where
owner = 'SYS' and
table_name = 'USER_TAB_COMMENTS'
;
--
-- JSON_ARRAYAGG(COLUMN_NAME)
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ["TABLE_NAME","TABLE_TYPE","COMMENTS","ORIGIN_CON_ID"]