DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • JSON-Based Serialized LOB Pattern
  • Introduction to Couchbase for Oracle Developers and Experts: Part 2 - Database Objects
  • FHIR Data Model With Couchbase N1QL
  • Keep Calm and Column Wise

Trending

  • How Can Developers Drive Innovation by Combining IoT and AI?
  • How to Build Real-Time BI Systems: Architecture, Code, and Best Practices
  • Customer 360: Fraud Detection in Fintech With PySpark and ML
  • Designing a Java Connector for Software Integrations
  1. DZone
  2. Data Engineering
  3. Data
  4. Introduction to Couchbase for Oracle Developers and Experts: Part 3: Data Types

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

In this blog series, we are exploring various topics to compare Oracle and Couchbase from a developer perspective. Today, we are going to talk about data types.

By 
Keshav Murthy user avatar
Keshav Murthy
DZone Core CORE ·
Jan. 27, 22 · Tutorial
Likes (4)
Comment
Save
Tweet
Share
6.8K Views

Join the DZone community and get the full member experience.

Join For Free
Article Image

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 Part 1 and Part 2 of this series. 

Snapshot - Data Remodeling

ReModeling: From Relational to Document Model


Oracle Couchbase
Model Relational, Object-relational JSON model with N1QL (SQL for JSON)
Data Types A 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 Does 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 explicitly: CREATETYPE person_typ ASOBJECT


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 a 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

An 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 date-time type.   We’ve chosen ISO 8601. The idea is to store the date and time-related data in a string form, conforming to ISO 8601 and then manipulating it in a consistent way.  See this blog with a detailed comparison to Oracle types, conversion, and formatting functions you can use in N1QL to extract and manipulate the date and time data.

The 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 to 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 for examples of how to do this correctly.

Data (computing) Data Types Database Relational database JSON Object (computer science)

Published at DZone with permission of Keshav Murthy, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • JSON-Based Serialized LOB Pattern
  • Introduction to Couchbase for Oracle Developers and Experts: Part 2 - Database Objects
  • FHIR Data Model With Couchbase N1QL
  • Keep Calm and Column Wise

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!