Introduction to Couchbase for Oracle Developers & Experts: Part 3: Data types

As part of data remodeling, while moving from the relational model to the JSON model, you’ll have to consider the data type mapping. In Oracle, you’ll have to create and declare the types of each column explicitly before you load the data or write queries. In Couchbase, you simply conform to JSON syntax and the data type interpretation is automatic and implicit. Here’s the overview of mappings, conversion, and arithmetic on these data types.

[Here’s the home page for the series: http://blog.planetnosql.com/2022/01/introduction-to-

couchbase-for-oracle.html]

ReModeling: From relational to Document model

 

	Oracle	Couchbase
Model	Relational, Object-relational	JSON model with N1QL (SQL for JSON)
Data Types	Long list of data types	https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datatypes.html
String Data types	CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, LOONGVARCHAR	String, up to 20MB in length. All of the string data in JSON is Unicode.
Date and Time Data Types	DATE, TIME, TIMESTAMP, all these with TIMEZONE, INTERVAL	Date, Time, Timestamp, all these with timezones should be represented in ISO 8601 format and saved as strings. N1QL has extensive functions for formatting, extraction, conversion, and arithmetic. This article covers conversion and arithmetic on them in detail. https://blog.couchbase.com/dates-timestamps-timezones-comparative-study-oracle-n1ql-support-date-time-feature-part-1/
Boolean	BOOLEAN	Boolean  true and false are automatically interpreted as a boolean value.
Numerical	NUMBER DECIMAL BIGINT SMALLINT INTEGER FLOAT REAL DOUBLE	Numeric data: can be integer, fraction or an exponent.  Here are the ranges: https://docs.couchbase.com/server/current/analytics/9_data_type.html
Binary Data Types	BINARY VARBINARY LONGVARBINARY	You can store the whole document as binary or encode the binary as base64 values.
Large Object Data Types
	BLOB, RAW, LONG_RAW	Each document can be up to 20 MB.  Binary data can be encoded via BASE64
	CLOB	Each document can be up to 20 MB.
	ABSTRACT Types, NESTED TABLES	Builtin support for objects, arrays, arrays of objects, objects of arrays. No support for user defined opaque data types.
	XML	Anyone still use XML in databases? ;-)
	ANY TYPE	ANY TYPE was invented to make the Oracle routines flexiblle — in terms of types they handle as parameters and return types.  In JSON model,
Objects	CREATE the object types and the columns associated with it explicity: CREATE TYPE person_typ AS OBJECT URL: https://docs.oracle.com/en/database/oracle/oracle-database/18/adobj/key-features-object-relational-model.html#GUID-0C7CE261-467B-4F8B-A04D-B0900E1FB284	OBJECTs are built into JSON model with any number of nested levels with any data type, objects or arrays.
Arrays	VARRAY can create an array of a specific type and then use that type as the type for a column.  URL: https://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#i468598	Array is a first class citizen in the JSON data model and can be used for any value. Each array can be of scalars of any type: scalars, objects, arrays, etc. “A”: [1, “X”, [3, 4]] “B”: [{“x”:1}, {“x”:2}]

Additional Notes

Date and Datetime types.

Oracle and other RDBMS have extensive data types for handling time-related data and manipulating them. JSON does not have a date or datetime type.   We’ve chosen the ISO 8601 . The idea is to store the date & time related data in a string form, conforming to ISO 8601 and then manipulating it in a consistent way.  See this blog with detailed comparison to Oracle types, conversion and formatting functions you can use in N1QL to extract and manipulate date and time data.

Primary key to Document key conversion.

Couchbase document key is always less than 255 bytes and is usually a string. RDBMS can have a single column or multiple columns (composite) primary key for a table. One common way convert is to simply have a separator between the individual parts after converting each part to a string. The document key should be unique to a bucket and hence it’s typically prefixed with the table (collection) type.  See the blog with examples of how to do this correctly.