I need to use arrays in Oracle SQL (a recursive CTE)
- hence posting under SQL & PLSQL (not ORDS/SODA/JSON etc)
From various other threads, looks like JSON is the best datatype for my needs.
Give an JSON_ARRAY, how do I append a single scalar / element ?
I (wrongly) assumed it would be as simple as Postgres array_append() function.
Came across json_transform / append but not sure how to invoke (below is pure guesswork - unsurprisingly does not work)
I would like this statement to return 2 rows.
with v (ja1) as ( select json_array(1,22,4 returning json) union all select json_array(2,33,8 returning json) union all select json_array(1,22,4,5 returning json)
union all select json_transform( json_array(1,22,4 returning json ), append '$.*' = json_scalar(5) ) )
select v.* from v where ja1 = json_array(1,22,4,5 returning json) ;
Syntax above does not error but is not correct.
NB Order of elements is important (must be preserved). Also datatype of elements is important but not sure if relevant to this question.
I am new to JSON so forgive me if I use incorrectly terms.
Version =
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production Version 23.4.0.24.05