DEV Community

Akmal Chaudhri for SingleStore

Posted on • Updated on

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"
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

We'll now create our database:

CREATE DATABASE json_demo;
Enter fullscreen mode Exit fullscreen mode

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;

CREATE TABLE test1 (
    whole JSON NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

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 <- %
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode

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:

CREATE TABLE test2 (
    URL TEXT,
    id JSON NOT NULL,
    address1 TEXT,
    address2 TEXT,
    name TEXT,
    outcode TEXT,
    postcode TEXT,
    rating FLOAT,
    type_of_food TEXT
);
Enter fullscreen mode Exit fullscreen mode

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 ""
);
Enter fullscreen mode Exit fullscreen mode

We can query the table as follows:

SELECT * FROM test2 LIMIT 1\G
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)