Multi-model data types

In OceanBase Database, semi-structured data types such as JSON, 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: For example, 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 example, 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: For example, OceanBase Database allows you to use Geographic Information System (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/GIS data types

JSON data type

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 the JSON data type, see JSON data types.

XML data type

XMLType is a 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 the XML data type, XMLType data types.

Spatial data type

Spatial data type is a data type used to store and process geographic spatial information. The Oracle mode of OceanBase Database supports the spatial data type SDO_GEOMETRY. SDO_GEOMETRY is used to store and process geometric data and is a composite data type used to represent two-dimensional or three-dimensional geometries.

The current version of OceanBase Database (Oracle mode) only supports 7 spatial object types defined by the OpenGIS Consortium (OGC), including Point, Linestring, Polygon, Multipoint, Multilinestring, Multipolygon, and Collection, which are composed of a connected sequence of points. In terms of spatial dimensions, two-dimensional and three-dimensional spatial data are supported.

For more information about spatial data types, see Spatial data types.