Multi-model data types

In OceanBase Database, semi-structured data types such as JavaScript Object Notation (JSON), geographic information system (GIS), and XML are classified as multi-model data types.

Characteristics of multi-model data

• Standard composition: For example, RFC 8259 is a data interchange specification for JavaScript Object Notation (JSON), while Open Geospatial Consortium (OGC) provides a set of text markup language (WKT-CRS) to describe coordinate systems and inter-system conversions, and World Wide Web Consortium (W3C) defines Extensible Markup Language (XML).
• Direct access based on SQL: As semi-structured data, multi-model data uses basic internal data types that can be mapped to SQL types. Multi-model data adopts a cascading logical tree schema that organizes a series of basic SQL types into complex composite data types.
• Fixed computing functions: Multi-model data types adopt standardized formats, which not only define the data exchange formats but also contribute to the computing APIs.

Query and computing of multi-model data

Multi-model data features a flexible structure. OceanBase Database provides a wide range of functions to support the construction, query, and computing of multi-model data based on the standards of multi-model data.

• Data construction: OceanBase Database provides aggregation functions that extract basic data from relational tables based on certain aggregation rules to construct JSON and XML objects.
• Data query: For JSON and XML data, OceanBase Database allows you to access a subset of multi-model data objects or a specific basic SQL element based on paths.
• Data computing: OceanBase Database allows you to use GIS functions to compute the relationships between spatial objects.

Storage of multi-model data

As a type of composite data, multi-model data is generally at a variable length, and a multi-model data object can sometimes be quite large. For storage convenience, multi-model data generally inherits the storage characteristics of LOB data. Technically, the storage upper limit of LOB data also applies to multi-model data. However, as the computing and storage of a large multi-model data object is very inefficient, we recommend that you do not construct a large multi-model data object.

• CLOB or VARCHAR2 type: You can store multi-model data directly as the CLOB or VARCHAR2 type in the database based on the standard text format. The disadvantage of this method is that each query of multi-model data requires parsing. Therefore, we recommend that you do not use this method to store multi-model data.
• Native multi-model type: The format of the stored data is optimized for database parsing. Once multi-model data is stored in the database, it will not be parsed again in the next query, and the storage space is also optimized. We recommend that you use the native multi-model type for storage.

Multi-model data interchange

Multi-model data is interchanged between different systems based on the standard format specifications.

JSON, XML, and GIS data types

JSON data types

OceanBase Database supports the JSON data type that complies with ECMA-404 and IETF RFC 8259. According to the JSON standard, JSON values can be objects, arrays, numbers, strings, Boolean values (true or false), or NULL, which are all JSON data types. All values except objects and arrays are scalar values.

In JSON data types, each attribute name and string value must be enclosed in double quotation marks (").

A JSON string consists of Unicode characters escaped with backslashes (\). JSON numbers are expressed in decimal notation. Object attributes are usually called fields, and name-value pairs of objects are usually called object members, but members sometimes may represent only attributes. Elements of a JSON array are enclosed in brackets ([ ]). Each element is an object, array, or scalar value. Multiple elements are separated with commas (,), and the elements are sorted in order.

For more information about JSON data types, see Overview of JSON data types.

XML data types

XMLType is an XML data type in SQL. As an abstract data type, XMLType supports XMLType constructors and PL methods for various XML operations.

You can use XMLType as other SQL data types. For example, you can create an XMLType table or view. In a PL stored procedure, you can use XMLType data as parameters, return values, and variables.

For more information about XML data types, see Overview of XML data types.

Spatial data types

Spatial data types are used to store and process geospatial data. The Oracle-compatible mode of OceanBase Database supports the SDO_GEOMETRY spatial data type. SDO_GEOMETRY is used to store and process geometric data. It is a composite data type used to represent a two-dimensional or three-dimensional geometric figure.

The current version of OceanBase Database's Oracle-compatible mode supports only the following spatial object types defined by OpenGIS Consortium (OGC): Point, Linestring, Polygon, Multipoint, Multilinestring, Multipolygon, and Collection. Such a spatial object consists of a group of concatenated sequences of points. Both two-dimensional data and three-dimensional data are supported.

For more information about spatial data types, see Overview of spatial data types.