Generate rows from JSON (SDO_GEOMETRY)

I have a multi-part SDO_GEOMETRY that I can represent as JSON (Oracle 21c):

select
    json_object(* PRETTY) as  pretty_json
from
   (
    select
        '001' as line_id,
        sdo_geometry('MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))') as sdo_geom
    from
        dual
    )

PRETTY_JSON
--------------------------
{
  "LINE_ID" : "001",
  "SDO_GEOM" :
  {
    "SDO_GTYPE" : 2006,
    "SDO_SRID" : null,
    "SDO_POINT" :
    {
    },
    "SDO_ELEM_INFO" :
    [
      1,
      2,
      1,
      7,
      2,
      1
    ],
    "SDO_ORDINATES" :
    [
      0,
      5,
      10,
      10,
      30,
      0,
      50,
      10,
      60,
      10
    ]
  }
}

---

As an experiment, I want to find a way to generate rows from the JSON:

   LINE_ID   PART_NUM VERTEX_NUM          X          Y 
---------- ---------- ---------- ---------- ---------- 
       001          1          1          0          5 
       001          1          2         10         10 
       001          1          3         30          0 
       001          2          1         50         10 
       001          2          2         60         10 

Each row is a vertex of the geometry.

Question:

In an SQL query, is there a way to generate rows for each of the JSON vertices?

---

I’m aware that SDO_GEOMETRY has a GetVertices() function that can be used for this — it avoids the JSON step. That works, but in my case, I want to experiment with parsing vertices out of JSON text using SQL. If I find something that works, I want to use that same JSON extraction technique on a user-defined datatype that doesn’t have a GetVertices function.

Answer

The simplest method (adapted from my answer to your previous question for 3D lines) is to use SDO_GEOMETRY and not to use JSON functions:

SELECT t.line_id,
       e.elem_no,
       v.id AS coord_id,
       x,
       y
FROM   ( SELECT '001' AS line_id,
                SDO_GEOMETRY(
                  'MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))'
                ) AS shape
         FROM   DUAL
       ) t
       CROSS JOIN LATERAL (
         SELECT LEVEL AS elem_no,
                SDO_UTIL.EXTRACT(t.shape, LEVEL) AS elem
         FROM   DUAL
         CONNECT BY LEVEL <= SDO_UTIL.GETNUMELEM(t.shape)
       ) e
       CROSS APPLY TABLE(SDO_UTIL.GETVERTICES(e.elem)) v

But, if you assume that you are only working with 2D multi-lines (GTYPE = 2006) then you can get the output with JSON but its not simple:

WITH elements (line_id, elem_no, starting_offset, next_offset, ordinates) AS (
  SELECT '001' as line_id,
         elem_no,
         starting_offset,
         next_offset,
         ordinates
  FROM   JSON_TABLE(
           JSON_OBJECT(
             SDO_GEOMETRY(
               'MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))'
             )
           ),
           '$?(@.SDO_GTYPE==2006)'
           COLUMNS (
             ordinates       VARCHAR2(4000) FORMAT JSON PATH '$.SDO_ORDINATES',
             NESTED PATH '$.SDO_ELEM_INFO[*]' COLUMNS (
               item_no FOR ORDINALITY,
               item    NUMBER PATH '$'
             )
           )
         )
  MATCH_RECOGNIZE(
    ORDER BY item_no
    MEASURES
      MATCH_NUMBER() AS elem_no,
      FIRST(starting_offset.item) AS starting_offset,
      FIRST(etype.item)           AS etype,
      FIRST(interpretation.item)  AS interpretation,
      NEXT(item)                  AS next_offset,
      FIRST(ordinates)            AS ordinates
    PATTERN (starting_offset etype interpretation)
    DEFINE starting_offset AS 1 = 1
  ) m
  WHERE (etype, interpretation) IN ((2, 1))
)
SELECT m.*
FROM   elements e
       CROSS JOIN LATERAL(
         SELECT *
         FROM   JSON_TABLE(
                  e.ordinates,
                  '$[*]'
                  COLUMNS (
                    coord_no FOR ORDINALITY,
                    coord    NUMBER PATH '$'
                  )
                )
         WHERE  coord_no >= e.starting_offset
         AND    (e.next_offset IS NULL OR coord_no < e.next_offset)
       )
MATCH_RECOGNIZE (
  PARTITION BY line_id, elem_no
  ORDER BY coord_no
  MEASURES
    MATCH_NUMBER() AS coord_id,
    FIRST(x.coord) AS X,
    FIRST(y.coord) AS Y
  PATTERN (X Y)
  DEFINE X AS 1 = 1
) m

Which both output:

LINE_ID	ELEM_NO	COORD_ID	X	Y
001	1	1	0	5
001	1	2	10	10
001	1	3	30	0
001	2	1	50	10
001	2	2	60	10

db<>fiddle here