Skip to main content

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-

ReModeling: From relational to Document model

 

OracleCouchbase
ModelRelational, Object-relationalJSON model with N1QL (SQL for JSON)
Data TypesLong list of data typeshttps://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datatypes.html
String Data typesCHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, LOONGVARCHARString, up to 20MB in length. All of the string data in JSON is Unicode.
Date and Time Data TypesDATE, TIME, TIMESTAMP, all these with TIMEZONE, INTERVALDate, 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/

BooleanBOOLEANBoolean 

true and false are automatically interpreted as a boolean value.

NumericalNUMBER

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 TypesBINARY

VARBINARY

LONGVARBINARY

You can store the whole document as binary or encode the binary as base64 values.
Large Object Data Types
BLOB, RAW, LONG_RAWEach document can be up to 20 MB.  Binary data can be encoded via BASE64
CLOBEach document can be up to 20 MB. 
ABSTRACT Types, NESTED TABLESBuiltin support for objects, arrays, arrays of objects, objects of arrays. No support for user defined opaque data types.
XMLAnyone still use XML in databases? ;-) 
ANY TYPEANY TYPE was invented to make the Oracle routines flexiblle — in terms of types they handle as parameters and return types. 

In JSON model, 

ObjectsCREATE 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.
ArraysVARRAY 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.

 

Labels:

Comments

Post a Comment

Popular posts from this blog

MongoDB to Couchbase for Developers: Part 2: Database Objects

    MongoDB developers and DBAs work with physical clusters, machines, instances, storage systems, disks, etc. All MongoDB users, developers, and their applications work with logical entities: databases, collections, documents, fields, shards, users, data types.  There are a lot of similarities with Couchbase since both are document (JSON) oriented databases.  Let’s compare and contrast the two with respect to database objects.  Here’s the first part of this series comparing the architecture.   MongoDB Organization A full list of MongoDB schema objects is listed here and here . A database instance can have many databases; A database can have many collections; Each collection can have many indexes.  Each collection can be sharded based into multiple chunks on multiple nodes of a cluster using hash-sharding strategy or index sharding strategy.  The MongoDB indexes are local to their data.  Therefore, the indexes use the same strategy as ...
3 comments
Read more

Swami Vivekananda: The Monk That Nobody Sent to Chicago

  There’s a saying in Chicago: “We don’t want nobody that nobody sent.” This was the cold reception Swami Vivekananda faced when he arrived in the windy city in July 1893, determined to attend the World Parliament of Religions that September. He belonged to no organization, carried no letter of recommendation, his countrymen were nobody, and represented an alien religion to the Western world. As the days passed, his hope of attending the parliament dwindled. With money running out and the odds stacked against him, he left the Windy City and went to Boston, praying for a glimmer of opportunity.  Swamiji came to America to share India’s most profound gift: the wisdom of the Hindu sages, preserved through centuries of oral tradition and embodied by its monks. This was 1893, not 1993—India was under the British grip, its resources drained, and its spirit subdued. Swamiji’s mission was not just a cultural exchange; it was a bold step toward envisioning a future where India could re...
Post a Comment
Read more

MongoDB to Couchbase for Developers: Part 1: Architecture

  Introduction By any measure, MongoDB is a popular document-oriented JSON database. In the last dozen years, it has grown from its humble beginnings of a single lock per database to a modern multi-document transaction with snapshot isolation. MongoDB University has trained a large number of developers to develop the MongoDB database. There are many JSON databases now. While it's easy to start with MongoDB to learn NoSQL and flexible JSON schema, many customers choose Couchbase for performance, scale, and SQL. As you progress in your database evaluation and evolution, it's easier to start from what you know now. That's the idea for this series. Use your MongoDB knowledge to easily learn and eventually master Couchbase.  Don't despair if you don't know MongoDB do know any one of the RDBMS. This Oracle to Couchbase series will help you learn by comparison.  Many nations are known by their language: French, German, Spanish). Many databases are known for their language...
1 comment
Read more