Quick tip: Moving MongoDB JSON data to SingleStoreDB

Abstract

SingleStoreDB is a multi-model database system and supports the ability to store and query JSON data. In this article, we'll see how easy it is to move a JSON database dump from MongoDB to SingleStoreDB.

Introduction

MongoDB provides several sample datasets for its database product. In this article, we'll look at how we can take one of these datasets and easily import it into SingleStoreDB.

Create a SingleStoreDB Cloud account

A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use JSON Demo Group as our Workspace Group Name and json-demo as our Workspace Name. We'll make a note of our password and host name.

MongoDB dataset

We'll use the restaurants dataset from MongoDB. It can be widely found on GitHub. The structure is quite flat. Here is an example from the dataset:

{
   "URL":"http://www.just-eat.co.uk/restaurants-cn-chinese-cardiff/menu",
   "_id":{
      "$oid":"55f14312c7447c3da7051b26"
   },
   "address":"228 City Road",
   "address line 2":"Cardiff",
   "name":".CN Chinese",
   "outcode":"CF24",
   "postcode":"3JH",
   "rating":5,
   "type_of_food":"Chinese"
}

SingleStoreDB can handle more complex JSON, as shown in the article Using SingleStore as a JSON Document Database.

Load JSON data into SingleStoreDB

First, let's connect to SingleStoreDB Cloud using a MySQL client:

mysql --local-infile \
      -u admin \
      -h <host> \
      -P 3306 \
      --default-auth=mysql_native_password \
      -p

The <host> being replaced with the value obtained from SingleStoreDB Cloud.

We'll now create our database:

CREATE DATABASE IF NOT EXISTS json_demo;

When loading the JSON data into SingleStoreDB, there are several options we could use. Let's look at two possibilities in this article.

1. Store JSON data in a single table column

In this option, each row of the JSON data is stored in a single table column.

First, we'll create a database table:

USE json_demo;

DROP TABLE IF EXISTS test1;
CREATE TABLE IF NOT EXISTS test1 (
    whole JSON NOT NULL
);

By using NOT NULL, SingleStoreDB will raise an error if there is an attempt to store invalid JSON.

Next, we'll load the JSON data as follows:

LOAD DATA LOCAL INFILE '/path/to/restaurants.json'
INTO TABLE test1
FORMAT JSON (
    whole <- %
);

We would replace /path/to/ with the actual path to where the JSON file was located.

We can query the table as follows:

SELECT * FROM test1 LIMIT 1\G

The result should be similar to the following:

*************************** 1. row ***************************
whole: {"URL":"http://www.just-eat.co.uk/restaurants-007takeaway-s65/menu","_id":{"$oid":"55f14312c7447c3da7051b2b"},"address":"6 Drummond Street","address line 2":"Rotherham","name":"007 Takeaway","outcode":"S65","postcode":"1HY","rating":6,"type_of_food":"Pizza"}

2. Store JSON data in multiple table columns

In this option, each row of the JSON data is stored in multiple table columns.

First, we'll create a database table:

USE json_demo;

DROP TABLE IF EXISTS test2;
CREATE TABLE IF NOT EXISTS test2 (
    URL TEXT,
    id JSON NOT NULL,
    address1 TEXT,
    address2 TEXT,
    name TEXT,
    outcode TEXT,
    postcode TEXT,
    rating FLOAT,
    type_of_food TEXT
);

Here we have used TEXT for many of the fields for initial testing.

Next, we'll load the JSON data as follows:

LOAD DATA LOCAL INFILE '/path/to/restaurants.json'
INTO TABLE test2
FORMAT JSON (
    URL <- URL default "",
    id <- _id,
    address1 <- address default "",
    address2 <- `address line 2` default "",
    name <- name default "",
    outcode <- outcode default "",
    postcode <- postcode default "",
    rating <- rating default NULL,
    type_of_food <- type_of_food default ""
);

We can query the table as follows:

SELECT * FROM test2 LIMIT 1\G

The result should be similar to the following:

*************************** 1. row ***************************
         URL: http://www.just-eat.co.uk/restaurants-328chineseonline-cf81/menu
          id: {"$oid":"55f14312c7447c3da7051b55"}
    address1: 2-4 Commercial Street
    address2: Aberbargoed
        name: 328 Chinese Online
     outcode: CF81
    postcode: 9BW
      rating: 5
type_of_food: Chinese

Summary

SingleStoreDB is a very capable and flexible database product with support for JSON data. In this short article, we have seen two options to store MongoDB JSON data in SingleStoreDB. The documentation contains further details and examples.