SDO_UTIL.TO_JSON

Format

SDO_UTIL.TO_JSON_VARCHAR(
     geometry  IN SDO_GEOMETRY 
     ) RETURN VARCHAR2;

Description

Converts an SDO_GEOMETRY object to a JSON object in VARCHAR2 format.

Parameters

geometry: Geometry in SDO_GEOMETRY format to be converted to a JSON object.

Usage Notes

For information about using JSON data that is stored in Oracle Database, see Oracle Database JSON Developer's Guide.

For information about Spatial support for JSON, see JSON and GeoJSON Support in Oracle Spatial.

This function (which returns a VARCHAR2 result) runs faster than the SDO_UTIL.TO_JSON (which returns a CLOB result). However, this function can be used only on very small geometries. Any geometry that generates more that 4000 bytes of JSON (or 32767 bytes if the database parameter MAX_STRING_SIZE is set to EXTENDED) results in a truncated and invalid JSON object.

To convert a geometry in JSON format to an SDO_GEOMETRY object, use the SDO_UTIL.FROM_JSON function.

Examples

The following example shows conversion to and from JSON format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.)

DECLARE
  cola_b_geom   SDO_GEOMETRY;
  returned_geom SDO_GEOMETRY;
  returned_json VARCHAR2(4000);
BEGIN
  -- Populate geometry variable with cola market cola_b shape.
  SELECT c.shape into cola_b_geom FROM cola_markets c
  WHERE c.name = 'cola_b';
  -- From geometry to JSON
  returned_json := SDO_UTIL.TO_JSON_VARCHAR(cola_b_geom);

  -- From JSON to geometry
  returned_geom := SDO_UTIL.FROM_JSON(returned_json);
END;
/

The following example shows the GeoJSON object that represents a specified geometry. (In this case the geometry definition reflects the cola_b geometry from the COLA_MARKETS table, defined in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT SDO_UTIL.TO_JSON_VARCHAR(
  SDO_GEOMETRY(2003, NULL, NULL, 
    SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
    SDO_ORDINATE_ARRAY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))) JSON 
FROM DUAL;  
JSON
------
{"polygon": {"boundary": [{"line": {"datapoints": [[5.0, 1.0], [8.0, 1.0], [8.0, 6.0], [5.0, 7.0], [5.0, 1.0]]}}]}}