In my last post When SQL Meets NoSQl, we talked about MariaDB can combine both SQL and NoSQL in the same database engine. Today, let’s find out how.

We’re going to use Docker image to start a MariaDB container, then try out CRUD (Create/ Read/ Update/ Delete) on JSON documents stored in the same relational database table.

Start MariaDB in a Docker container

% docker run --name mariadb -e MYSQL_ROOT_PASSWORD=example -d mariadb:10.4.12

check if it’s started.

% docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
6f0dc37d8811        mariadb:10.4.12     "docker-entrypoint.s…"   4 minutes ago       Up 4 minutes        3306/tcp            mariadb

Open the MariaDB CLI in the container

% docker exec -it mariadb bash
root@6f0dc37d8811:/# mysql -uroot -pexample
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.12-MariaDB-1:10.4.12+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Prepare a table to host JSON documents

MariaDB [(none)]> create database mydb;
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> create table if not exists products (
    -> sku int not null primary key,
    -> name varchar(40) not null,
    -> price varchar(40) not null,
    -> qty int not null,
    -> attr json not null
    -> );

If you look at field attr, its data type is json. json is a new data type used to store JSON documents. Ok, we already have an empty table ready to store some data. Let’s put some JSON documents into it.

Create

We’ll insert below two records into table products.

MariaDB [mydb]> insert into products values
(1, "Laptop", "$1100", 235,
 '{"RAM":"8GB DDR3", "audio":["DTS","Dolby"], "video":{"resolution":"1080P", "aspectRatio":"16:9"}}');

MariaDB [mydb]> insert into products values
(2, "The Little Prince", "$10", 600, 
 '{"Publisher":"Rupa Publications", "ISBN":"9353044405", "Language":"English"}');

Now, let’s find out how the JSON documents look like in the database.

MariaDB [mydb]> select * from products;
+-----+-------------------+-------+-----+---------------------------------------------------------------------------------------------------+
| sku | name              | price | qty | attr                                                                                              |
+-----+-------------------+-------+-----+---------------------------------------------------------------------------------------------------+
|   1 | Laptop            | $1100 | 235 | {"RAM":"8GB DDR3", "audio":["DTS","Dolby"], "video":{"resolution":"1080P", "aspectRatio":"16:9"}} |
|   2 | The Little Prince | $10   | 600 | {"Publisher":"Rupa Publications", "ISBN":"9353044405", "Language":"English"}                      |
+-----+-------------------+-------+-----+---------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

Read

Query a single value from a JSON document

MariaDB [mydb]>  select sku, name, json_value(attr, '$.RAM') as ram
    ->  from products where sku=1;
+-----+--------+----------+
| sku | name   | ram      |
+-----+--------+----------+
|   1 | Laptop | 8GB DDR3 |
+-----+--------+----------+
1 row in set (0.001 sec)

JSON_VALUE() is used to read a single number or string. Parameter $.RAM is the path or you may call it key to the value you want to query. You can use a nested path like below.

MariaDB [mydb]> select json_value(attr, '$.video.resolution') as video from products where sku=1;
+-------+
| video |
+-------+
| 1080P |
+-------+
1 row in set (0.002 sec)

Query an array from a JSON document

MariaDB [mydb]> select json_query(attr, '$.audio') as audio from products where sku=1;
+-----------------+
| audio           |
+-----------------+
| ["DTS","Dolby"] |
+-----------------+
1 row in set (0.001 sec)

Function JSON_QUERY() is used to read an array. It has the same path parameter as JSON_VALUE(). Do you want to read the first element of the array? Follow below example:

MariaDB [mydb]> select json_value(attr, '$.audio[0]') as first_audio from products where sku=1;
+-------------+
| first_audio |
+-------------+
| DTS         |
+-------------+
1 row in set (0.001 sec)

You see, the first element is a single string, so you need to use JSON_VALUE() instead of JSON_QUERY().

Query an object from a JSON document

JSON_QUERY() is also used to query an object.

MariaDB [mydb]> select json_query(attr, '$.video') as video from products where sku=1;
+----------------------------------------------+
| video                                        |
+----------------------------------------------+
| {"resolution":"1080P", "aspectRatio":"16:9"} |
+----------------------------------------------+
1 row in set (0.001 sec)

Use JSON value in your where clause

you can query something based on one element of an array.

MariaDB [mydb]>  select sku,name,price
    ->    from products
    ->  where
    ->    sku="1" and json_contains(attr, '\"DTS\"', '$.audio')=1;
+-----+--------+-------+
| sku | name   | price |
+-----+--------+-------+
|   1 | Laptop | $1100 |
+-----+--------+-------+
1 row in set (0.012 sec)

JSON_CONTAINS() is used to check if a JSON array $.audio contains an element with value \"DTS\". Pay attention to the \" sign around DTS. It’s weird to do that. Why not support json_contains(attr, 'DTS', '$.audio') directly? I don’t know. Maybe someday MariaDB will allow us to do the latter.

You can also query based on a single value of a JSON document.

MariaDB [mydb]> select sku,name,price
    ->    from products
    -> where
    ->    sku=1 and json_value(attr, '$.video.resolution')='1080P';
+-----+--------+-------+
| sku | name   | price |
+-----+--------+-------+
|   1 | Laptop | $1100 |
+-----+--------+-------+
1 row in set (0.003 sec)

Update

Add a new field to a JSON document

MariaDB [mydb]> update products
    ->    set attr=json_insert(attr, '$.format', 'Paperback')
    ->  where sku=2;
Query OK, 1 row affected (0.028 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mydb]> select * from products where sku=2;
+-----+-------------------+-------+-----+--------------------------------------------------------------------------------------------------------+
| sku | name              | price | qty | attr                                                                                                   |
+-----+-------------------+-------+-----+--------------------------------------------------------------------------------------------------------+
|   2 | The Little Prince | $10   | 600 | {"Publisher": "Rupa Publications", "ISBN": "9353044405", "Language": "English", "format": "Paperback"} |
+-----+-------------------+-------+-----+--------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Update existing field’s value

MariaDB [mydb]> update products
    ->   set attr=json_replace(attr, '$.Language', 'French')
    -> where sku='2';
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mydb]> select * from products where sku=2;
+-----+-------------------+-------+-----+-------------------------------------------------------------------------------------------------------+
| sku | name              | price | qty | attr                                                                                                  |
+-----+-------------------+-------+-----+-------------------------------------------------------------------------------------------------------+
|   2 | The Little Prince | $10   | 600 | {"Publisher": "Rupa Publications", "ISBN": "9353044405", "Language": "French", "format": "Paperback"} |
+-----+-------------------+-------+-----+-------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Delete

You can remove a field from a JSON document using JSON_REMOVE().

MariaDB [mydb]> update products
    ->   set attr=json_remove(attr, '$.format')
    -> where sku=2;
Query OK, 1 row affected (0.009 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mydb]> select * from products where sku=2;
+-----+-------------------+-------+-----+--------------------------------------------------------------------------------+
| sku | name              | price | qty | attr                                                                           |
+-----+-------------------+-------+-----+--------------------------------------------------------------------------------+
|   2 | The Little Prince | $10   | 600 | {"Publisher": "Rupa Publications", "ISBN": "9353044405", "Language": "French"} |
+-----+-------------------+-------+-----+--------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Now we have gone through the whole CRDU of JSON documents in MariaDB. Isn’t it so simple and intuitive? I hope you can use it in your project to simplify your design.