Using SingleStore as a JSON Document Database

Abstract

Continuing our series on the multi-model capabilities of SingleStore, in this article we’ll discuss SingleStore’s support for JSON data. Using examples, we’ll see SingleStore’s strengths in supporting JSON data, including arrays and nesting. We’ll also see how to query this data using SQL and how to build a quick visual front-end using Laravel and PHP.

The SQL scripts and PHP code files used in this article are available on GitHub.

Introduction

Previously, we discussed using SingleStore for Time Series data and Geospatial data and we’ll now continue our discussion on the multi-model capabilities of SingleStore by focussing on JSON data.

JSON is a popular data format today and can be extremely useful for applications that need to capture information about objects that may vary in their attributes. This would be particularly useful for applications such as e-commerce where we may be storing a range of products that have quite different attributes from each other. We’ll look at some examples of this shortly.

To begin with, we need to create a free Managed Service account on the SingleStore website. At the time of writing, the Managed Service account from SingleStore comes with $500 of Credits. This is more than adequate for the case study described in this article.

In this article, we’ll build a small inventory system to model an online store that sells a variety of electronic equipment. This example is derived from an excellent tutorial available on DigitalOcean. We’ll apply that tutorial to SingleStore and we’ll see that it is very easy to store, retrieve and query JSON data using SingleStore.

Create Database Tables

In our SingleStore Managed Service account, let’s use the SQL Editor to create a new database. Call this e_store, as follows:

We’ll also create the brands, categories and products tables, as follows:

There is a one-to-many (1:m) relationship between brands and products and between categories and products. The design could be improved but is sufficient for illustrating the main focus of this article, which is the JSON support.

We can see that the products table has a JSON column called attributes. By using NOT NULL on the attributes column, SingleStore will raise an error if there is an attempt to store invalid JSON.

Populate Database Tables

Let’s now populate the tables. First the brands table:

Next, the categories table:

Finally, the products table.

Televisions

First let’s load the data for Televisions:

If we examine the JSON data, we can see that there is nesting. For example:

SingleStore can handle nested JSON data with ease.

Mobile Phones

Next, let’s load the data for Mobile Phones:

In this case, rather than using JSON directly, we are building the JSON structure for storage using the JSON_BUILD_STORAGE function. We also have an array structure that we are creating using JSON_ARRAY_PUSH_STRING. An example of the JSON structure would look as follows:

Cameras

Finally, let’s load the data for Cameras:

In this case we have no nesting or arrays but a flat JSON structure. For example:

From these examples, we can see that we may need to store our JSON data in a variety of different ways and the structure of the data may vary depending upon the attributes we wish to store. SingleStore can handle these different requirements and comes with a wide-range of JSON functions that can help.

Example Queries

Now that our data are safely inside SingleStore, let’s look at some ways that we can query that data.

First, let’s see what SingleStore returns for the attributes column using JSON_GET_TYPE:

The result should be:

All the rows are JSON objects.

Now let’s find any Televisions that have one or more USB port(s) and one or more HDMI port(s):

Notice that we can use the double-colon (::) to specify a path to the specific attribute that we are interested in. The output should be:

Next, let’s try some update operations. First we’ll create a new attribute called body_color for Televisions, as follows:

If we check the attributes, we should see that body_color has been added:

Next, let’s add a chipset for Mobile Phones, as follows:

If we check the attributes, we should see that chipset has been added:

We can also update the existing value of the chipset to a new value, as follows:

If we check the attributes, we should see that chipset has been updated:

We can also delete attributes. For example, we know from our data that we created earlier that we have a mount_type for Cameras. We could delete this as follows:

If we check the attributes, we should see that mount_type has been deleted:

We can also write more complex operations. For example, here we are checking the os attribute using the LIKE operator. In our database, we have two Mobile Phones with the Jellybean OS.

After running the above command, we should have three Mobile Phones in our database:

SingleStore supports an extensive set of functions that can be used with JSON data. The documentation also contains further details and examples.

Bonus: Visualisation using Laravel and PHP

Running the commands in the previous sections using the SQL Editor in our Managed Service account is a great way to test our code and quickly view the results. However, we can go a step further and build a simple web interface that allows us to see the data and perform some Create, Read, Update and Delete (CRUD) operations. In this first application development iteration, we’ll focus mainly on Read, Delete and partial Update. We’ll build a more complete solution in the future.

We’ll delete the existing database and recreate it, so that we have the complete original dataset.

We’ll build our web interface using Laravel and PHP and use the following software:

We’ll also need to install Composer and we’ll follow the instructions on the download page. Once Composer has been downloaded, we’ll move it to the bin directory:

Next, we’ll create a project called e-store, as follows:

and then change to the project directory:

We’ll now edit the .env file in the e-store directory:

The <TO DO> for host and password should be replaced with the values obtained from the SingleStore Managed Service when creating a cluster. Note also the use of double-quotes (") for DB_USERNAME and DB_PASSWORD.

Create Files

A quick way to generate all the files we need is as follows:

For each of Brand, Category and Product, we obtain:

  • A migration, in database/migrations
  • A model in app/Models
  • A controller, in app/Http/Controllers
  • A seeder, in database/seeders

Migrations (database/migrations)

We’ll edit the Brand migration file, so that we have:

the Category migration file, so that we have:

and the Product migration file, so that we have:

Models (app/Models)

We’ll edit the Brand model file, so that we have the 1:m relationship with Product:

the Category model file, so that we have the 1:m relationship with Product:

and the Product model file, so that we have the ability to access the JSON data by casting the attributes to an array and the relationships with Brand and Category:

Controllers (app/Http/Controllers)

For this first iteration of the web application, let’s focus on the ProductController.

index()

For the products index page, we need to retrieve all the product data and ensure that we have the brand name and category name for each product. This will require joins across the respective tables. We’ll also control the output by showing just five records per page using simplePaginate().

show()

To show a single product, we’ll perform a query similar to the query for index() but we’ll use first() to get an individual product record.

edit()

To edit an existing product, we’ll need to find the product to edit and we’ll also need to get all the brands and categories so that they can be offered in drop-down menus if the user wishes to change these product attributes.

update()

We’ll allow updates to the product name and allow its brand and category to be changed but not its JSON attributes in this first application development iteration.

destroy()

We can remove a product very easily by just using delete().

Routes (routes/web.php)

In the web.php file in the routes directory, we’ll add the following:

Views (resources/views/admin)

The three blade files for the index page, show page and edit page can be found on GitHub. In those files, we’ll have code for formatting the data using HTML and PHP for presentation and partial editing.

Run the Code

We’ll run the application from the e-store directory, as follows:

In a web browser, we’ll enter the following:

The output should be similar to Figure 1:

We can see the Brand and Category data being correctly displayed for each product. The attributes in JSON format need improved presentation but we can see these correctly in this first iteration. If we select Show, we can view the details about a product on a single page, as shown in Figure 2.

From the index page, if we select Edit, we can edit the product as shown in Figure 3.

We can fully edit the name and change the Brand and Category. The JSON is not editable in this first iteration.

Finally, from the index page, if we select Delete, we can remove a product from the database. In Figure 4, the product with ID 1 has been deleted. We can confirm this by checking SingleStore through the SQL Editor in our Managed Service account.

Client-side handling of JSON for different Products

One of the challenges we face for data entry and update of the JSON on the client-side is that the structure is variable for each of the three different products. One solution proposed in the original DigitalOcean tutorial is to create a specific web page for each product type. This would be a good solution for a small number of products. However, if we were storing tens or hundreds of different products, this would not be viable.

The JSON data may be flat, it may be nested and it may have arrays. One way that we could deal with this is to apply a solution proposed on Stackoverflow, using recursion to determine the nodes and leaves so that the JSON structure is correctly output. We can demonstrate this using the following example:

The output should be:

This gives us all the correct keys and values.

Summary

In this article we have seen that SingleStore can manage JSON data of varying complexity with ease. It supports a wide-range of functions that can be used with JSON data and we have used a number of these functions in this article. Furthermore, we have seen that we can use SQL queries that combine operations on both Relational and JSON data. Finally, we have built a simple web interface to our database system using Laravel and PHP that enables us to explore the data and make some modifications as well.

I help to build global developer communities and raise awareness of technology through presentations and technical writing.