Select JSON text of SDO_GEOMETRY using SQL

User_1871

I have an multi-part SDO_GEOMETRY polyline.

How can I get the JSON text representation of that geometry?

User_1871

It looks like I can use the JSON_OBJECT() function in 21c: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_OBJECT.html

Unformatted JSON:

select
  json_object(sdo_util.from_wktgeometry( '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

Output:
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]}

https://dbfiddle.uk/?rdbms=oracle_21&fiddle=5e9b41a3f88877aee361e0dd6cc0fb64

Pretty JSON:

select
  json_object(* PRETTY)
from
  (
  select
    sdo_util.from_wktgeometry( '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
  )

Output:
JSON_OBJECT(*PRETTY)
-----------------------------
{
  "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
    ]
  }
}

https://dbfiddle.uk/?rdbms=oracle_21&fiddle=9cbba5ff5675f1337cd6e6b1bcd4fcb9

User_1871

A Follow-up question:

Is there a way to get the pretty formatting -- without using a subquery?

Bkazar-Oracle

Would You like to try the following?

SQL> select sdo_util.to_json( SDO_GEOMETRY(2006, null, null, SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1), SDO_ORDINATE_ARRAY(0.0, 5.0, 10.0, 10.0, 30.0 , 0.0, 50.0, 10.0, 60.0, 10.0))) json from dual;

JSON

--------------------------------------------------------------------------------

{"geometrycollection": {"geometries": [{"line": {"datapoints": [[0.0, 5.0], [10.

0, 10.0], [30.0, 0.0]]}}, {"line": {"datapoints": [[50.0, 10.0], [60.0, 10.0]]}}

]}}

Best regards