SQL & PL/SQL

Struggling with basic concept such as intersection between 2 arrays.

Using json_arrays because they seems most appropriate (data types, collation, SQL native) etc

I cannot get PREPEND to work as per documentation either.

https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/oracle-sql-function-json_transform.html

For example, supposing that array a has value [30,20] and array b has value [2,4,6,8].

PREPEND '$.a' = PATH '$.b'

PREPEND '$.a' = PATH '$.b[2,4]'

PREPEND '$.a' = PATH '$.b[*]'

with    v ( a, b) as ( select json_array(30,20 returning json), json_array(2,4,6,8 returning json) )
select a, b
       , json_transform( a, PREPEND '$.a' = PATH '$.b' )           as c
       , json_transform( a, PREPEND '$.a' = PATH '$.b[2,4]' )      as d
       , json_transform( a, PREPEND '$.a' = PATH '$.b[*]' )        as e
from v ;

SQL> with v ( a, b) as ( select json_array(30,20 returning json), json_array(2,4,6,8 returning json) )
2 select a, b
3 , json_transform( a, PREPEND '$.a' = PATH '$.b' ) as c
4 , json_transform( a, PREPEND '$.a' = PATH '$.b[2,4]' ) as d
5 , json_transform( a, PREPEND '$.a' = PATH '$.b[*]' ) as e
6 from v ;

A B C D E
--------------- --------------- --------------- --------------- ---------------
[30,20] [2,4,6,8] [30,20] [30,20] [30,20]

So for INTERSECT I tried something similar - with no real clue what is going on

-- should return json_array = [2,8]
with    v ( a, b) as ( select json_array(16,8,2 returning json), json_array(2,4,6,8 returning json) )
select a, b
        , json_transform( a, intersect '$.a' = PATH '$.b' )         as c
        , json_transform( a, intersect '$.a' = PATH '$.b[2,4]' )    as d
        , json_transform( a, intersect '$.a' = PATH '$.b[*]' )      as e
from v ;

SQL> -- should return json_array = [2,8]
SQL> with v ( a, b) as ( select json_array(16,8,2 returning json), json_array(2,4,6,8 returning json) )
2 select a, b
3 , json_transform( a, intersect '$.a' = PATH '$.b' ) as c
4 , json_transform( a, intersect '$.a' = PATH '$.b[2,4]' ) as d
5 , json_transform( a, intersect '$.a' = PATH '$.b[*]' ) as e
6 from v ;

A B C D E
--------------- --------------- --------------- --------------- ---------------
[16,8,2] [2,4,6,8] [16,8,2] [16,8,2] [16,8,2]