Introduction to Couchbase for Oracle Developers and Experts: Part 3: Data Types

item picture

As part of data refactoring, during the transition from relational model to JSON model, you will have to consider setting the datatype. In Oracle, you will have to explicitly create and declare the types of each column before loading data or writing queries. In Couchbase, you simply conform to the JSON syntax and the interpretation of the data type is automatic and implicit. Below is an overview of assignments, conversions, and calculations on these data types.

Snapshot - data replay

Reconfiguration: From relational to document model

inspiration a sofa
Model relational, object relational JSON form with N1QL (SQL for JSON)
Data types Long list of data types
String data types CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, LOONGVARCHAR String, up to 20MB in length. All string data in JSON is Unicode.
Date and time data types DATE, TIME, TIMESTAMP, ALL THESE WITH TIMEZONE, INTERVAL The date, time, timestamp and all of these time zones must be represented in ISO 8601 format and saved as strings. N1QL has extensive functions of formatting, extracting, transforming and calculating.

This article discusses conversion and calculation on it in detail.

Oracle Date Format: N1QL and Support for Date-Time Functions – Pt 1

Boolean Logical Boolean

correct And False It is automatically interpreted as a Boolean value.

numerical Number

Decimal number







Numeric data: can be an integer, a fraction, or an exponent. Here are the domains:
Binary data types Binary



You can store the entire document as binary or encode the binary as base64 values.
Big Object Data Types
BLOB, RAW, LONG_RAW Each document can be up to 20 MB in size. Binary data can be encrypted via BASE64
CLOB Each document can be up to 20 MB in size.
Abstract types, nested tables Built-in support for objects, arrays, arrays of objects, and array objects. No support for user-defined opaque data types.
XML Does anyone still use XML in databases? 😉
what kind ANY TYPE was invented to make Oracle routines flexible – in terms of the types you treat as parameters and return types.

In a JSON form,

Things Explicitly create object types and their associated columns: CreateType person_type asopposing


Objects are included in a JSON model with any number of levels nested with any data type, objects, or arrays.
matrices VARRAY can create an array of a certain type and then use that type as a column type.


The array is a first-class citizen in the JSON data model and can be used for any value. Each array can be of any type: volumetrics, objects, arrays, etc.

“a”: [1, “X”, [3, 4]]

“B”: [{“x”:1}, {“x”:2}]

Additional Notes

Types of date and time

Oracle and other RDBMS systems have extensive data types for handling and processing time-related data. JSON does not have a date or datetime type. We chose ISO 8601. The idea is to store the date and time data in the form of a string corresponding to ISO 8601 and then process it in a consistent manner. Check out this blog with a detailed comparison of the Oracle types and the transform and format functions you can use in N1QL to extract and manipulate date and time data.

The primary key to convert the document key.

The key of a Couchbase document is always less than 255 bytes and is usually a string. An RDBMS can contain one or multiple (compound) columns of a table’s primary key. One common way to convert is to simply have a separator between the individual parts after converting each part to a string. The document key must be unique to the container, and therefore is usually prefixed with the type of table (group). See the blog for examples of how to do it right.


Leave a Comment