By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with data (sdo_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
SELECT v.obj
FROM data d
CROSS JOIN LATERAL (
SELECT VALUE(t) AS obj
FROM TABLE(d.sdo_array) t
) v
with data (sdo_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
SELECT JSON_OBJECT(v.obj)
FROM data d
CROSS JOIN LATERAL (
SELECT VALUE(t) AS obj
FROM TABLE(d.sdo_array) t
) v
| JSON_OBJECT(V.OBJ) |
|---|
| {"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":10,"Y":20,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]} |
| {"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":30,"Y":40,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]} |
| {"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":50,"Y":60,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]} |