How to denormalize data by merging a table as an object into a document in couchbase

Sevcan Doğramacı

Follow

Published in

Modanisa Engineering

3 min readMay 16, 2022

--

Imagine you have data in RDBMS, and at some point, you have decided to move your structure to Couchbase. You investigated the data and decided that you will denormalize a table into a document in migration. In this post, we will see how to embed data in a table with relation to couchbase documents.

Data in RDBMS

Let’s say you have two tables in relation:

delivery_option table where you keep delivery options info.
delivery_option_translation table where you keep translations of delivery options.

delivery_option and delivery_option_translation tables

Data Design in Couchbase

Now, instead of creating two tables, you want to create one table for storing delivery options. You will eliminate delivery_option_translation table. But what happens to data there then?

The answer is simple. We will merge each delivery option’s translations into its document in Couchbase. So, a delivery option will look like this:

Sample delivery_option document in Couchbase

Migration

We can use couchbase:community-7.1.0 docker image to use couchbase in local.

Prepare couchbase

1. Run

docker run -p 8091-8096:8091-8096 couchbase:community-7.1.0

2. Setup couchbase cluster

Go to localhost:8091
Configure cluster name, admin username, and password settings
Create a bucket called sample
Create a collection called delivery_option in sample bucket _default scope
Create additional scope called migration
Create a collection called delivery_option in sample bucket migration scope
Create a collection called delivery_option_translation in sample bucket migration scope

3. Import delivery_option and delivery_option_translation data

Download sample data here.
Import delivery_option.json as JSON list into sample.migration.delivery_option keyspace using UUID.
Import delivery_option_translation.json as JSON list into sample.migration.delivery_option_translation keyspace using UUID.

4. Create indexes

We need several primary and secondary indexes before running the merge query. Run queries to create these indexes.

Queries to create indexes for merge query

5. Merge translations into delivery options and insert

Run the merge query to insert delivery options into delivery_option collection in _default scope.

Query to merge translations into delivery option

If we take a look at the query deeper, we create an object v.`language`: v.translation with all translations of a delivery option by looping over.

We use SELECT RAW OBJECT and [0] for eliminating the field attribute.
We use LEFT NEST for getting all translations even if there is no translation. (So that object will be empty.)
We use MERGE INTO ..... WHEN MATCHED ... query to insert or update new delivery options into a bucket.

Result

Voila 🥳 Here are the delivery options data migrated!

To sum up, we achieved to merge data as an object to couchbase documents. That was it for this post, see u in the next episodes 🧐 🚗

References: