Quick tip: Moving MongoDB JSON data to SingleStoreDB
Tags: singlestoredb, mongodb, json
Published: 2022-10-04 13:10:12

## 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](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) 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:

```json
{
   "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](https://medium.com/@VeryFatBoy/using-singlestore-as-a-json-document-database-bc386fc5c563).

## Load JSON data into SingleStoreDB
First, let's connect to SingleStoreDB Cloud using a MySQL client:

```shell
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:

```sql
CREATE DATABASE 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:

```sql
USE json_demo;

CREATE TABLE 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:

```sql
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:

```sql
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:

```sql
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
);
```

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

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

```sql
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:

```sql
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](https://docs.singlestore.com/managed-service/en/reference/sql-reference/data-manipulation-language-dml/load-data.html) contains further details and examples.