Skip to main content

Introduction to MySQL Document Store

First some context:
  • we will create a collection to add people in it
  • we will create a virtual column on the age
  • we will index that column
  • we will query and add records to that collection

Collection creation and add some users

mysql-js> schema = session.getSchema('docstore')

mysql-js> collection = schema.createCollection('users')

mysql-js> collection.add({name: "Descamps", firtname: "Frederic", age: "41"}).execute();
mysql-js> collection.add({name: "Cottyn", firtname: "Yvan", age: "42"}).execute();
mysql-js> collection.add({name: "Buytaert", firtname: "Kris", age: "41"}).execute();

mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select * from users;
+------------------------------------------------------------------------------------------------------+----------------------------------+
| doc                                                                                                  | _id                              |
+------------------------------------------------------------------------------------------------------+----------------------------------+
| {"_id": "06ab653c0c58e7117611685b359e77d5", "age": "41", "name": "Descamps", "firtname": "Frederic"} | 06ab653c0c58e7117611685b359e77d5 |
| {"_id": "9828dd6e0c58e7117611685b359e77d5", "age": "41", "name": "Buytaert", "firtname": "Kris"}     | 9828dd6e0c58e7117611685b359e77d5 |
| {"_id": "f24730610c58e7117611685b359e77d5", "age": "42", "name": "Cottyn", "firtname": "Yvan"}       | f24730610c58e7117611685b359e77d5 |
+------------------------------------------------------------------------------------------------------+----------------------------------+
3 rows in set (0.00 sec)

Virtual Column Creation

Usually when we create a virtual generated column, we do like this:
mysql-sql> alter table users add column age varchar(2) 
           GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.age'))) VIRTUAL;
Query OK, 0 rows affected (0.19 sec)

mysql-sql> select _id, age from users;
+----------------------------------+-----+
| _id                              | age |
+----------------------------------+-----+
| 06ab653c0c58e7117611685b359e77d5 | 41  |
| 9828dd6e0c58e7117611685b359e77d5 | 41  |
| f24730610c58e7117611685b359e77d5 | 42  |
+----------------------------------+-----+
The first question I got was related to the data type. As age are integers, could we use it as an integer too ?
The answer is of course yes, but be careful:
mysql-sql> alter table users drop column age;

mysql-sql> alter table users add column age int 
           GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.age'))) VIRTUAL;

mysql-sql> select _id, age from users;
+----------------------------------+-----+
| _id                              | age |
+----------------------------------+-----+
| 06ab653c0c58e7117611685b359e77d5 |  41 |
| 9828dd6e0c58e7117611685b359e77d5 |  41 |
| f24730610c58e7117611685b359e77d5 |  42 |
+----------------------------------+-----+
3 rows in set (0.00 sec)
mysql-sql> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) 
        GENERATED ALWAYS AS 
         (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,
  `age` int(11) 
        GENERATED ALWAYS AS 
         (json_unquote(json_extract(`doc`,'$.age'))) VIRTUAL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
Ok, so now we are using a virtual column as an integer…. but as you know know, NoSQL does’nt really care, we could add there any value of any type.
But what will MySQL think about it ?
Let’s verify:
mysql-js> collection.add({name: "Vanoverbeke", firtname: "Dimitri", age: "kid"}).execute();
Incorrect integer value: 'kid' for column 'age' at row 1 (MySQL Error 1366)
As you can see above, the virtual column causes an error and we are not able to add such value… but if we really want ? #freedomeverywhere !
But before, let’s remove that generated column and add the record first and then create again the virtual column:
mysql-sql> alter table users drop column age;
mysql-sql> \js
Switching to JavaScript mode...
mysql-js> collection.add({name: "Vanoverbeke", firtname: "Dimitri", age: "kid"}).execute();
Query OK, 1 item affected (0.05 sec)
mysql-sql> alter table users 
           add column age int 
           GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.age'))) VIRTUAL;
This works like a charm (see note at the end of the post)…
mysql-sql> select _id, age from users;
+----------------------------------+-----+
| _id                              | age |
+----------------------------------+-----+
| 06ab653c0c58e7117611685b359e77d5 |  41 |
| 9828dd6e0c58e7117611685b359e77d5 |  41 |
| ca4f7fba1058e7117611685b359e77d5 |   0 |
| f24730610c58e7117611685b359e77d5 |  42 |
+----------------------------------+-----+
4 rows in set (0.00 sec)
But we can’t add another one anyway:
mysql-js> collection.add({name: "Gryp", firtname: "Kenny", age: "teenager"}).execute();
Incorrect integer value: 'teenager' for column 'age' at row 1 (MySQL Error 1366)
So let’s remove the column and recreate it with an index on it too:
mysql-sql> alter table users drop column age;
mysql-sql> alter table users add column age int 
           GENERATED ALWAYS AS 
           (json_unquote(json_extract(`doc`,'$.age'))) VIRTUAL, add index age_idx(age) ;
ERROR: 1366: Incorrect integer value: 'kid' for column 'age' at row 1
Then it doesn’t work anymore, even with data already present in the collection.

CAST( )

OK, we should find another solution. Let’s try with the CAST() function that will return 0 if it cannot find a integer in the value:
mysql-sql> SELECT CAST("kid" AS UNSIGNED);
+-------------------------+
| CAST("kid" AS UNSIGNED) |
+-------------------------+
| 0                       |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
This seems to be what we are looking for, let’s use it:
mysql-sql> alter table users add column age int 
           GENERATED ALWAYS AS 
           (cast((json_unquote(json_extract(`doc`,'$.age'))) AS SIGNED)) 
           VIRTUAL, add index age_idx(age) ;
ERROR: 1292: Truncated incorrect INTEGER value: 'kid'
Not the same error, but it doesn’t work.
You might then think that instead of having a VIRTUAL column, we should STORE it and index it…
Unfortunately this is neither an option:
mysql-sql> alter table users add column age int 
           GENERATED ALWAYS AS 
           (cast((json_unquote(json_extract(`doc`,'$.age'))) AS SIGNED)) STORED;
ERROR: 1292: Truncated incorrect INTEGER value: 'kid'

And so ?

So the second question was if we type our fields and we want to index them, as in json they are not typed, what will happen ?
If you are not sure the same type will be used in the document for the same attribute, as you can see, it’s not working very well. Such check must be done in the application that uses MySQL Document Store or you will face some problems as described above.
But of course there is a solution (if there is no solution, there is no problem, isn’t it ?)

Solution

Instead of using the CAST() function we will create our generated column like a boss and use IF with an old trick of adding 0 (IF( ) with CAST() would also work):
mysql-sql> alter table users add column age int 
           GENERATED ALWAYS 
           AS (IF(doc->>"$.age"+0=0,NULL,doc->>"$.age")) VIRTUAL, WITH VALIDATION;
Query OK, 4 rows affected (0.81 sec)
I’ve also added WITH VALIDATION. This means that the ALTER TABLE copies the table and if an out-of-range or any other error occurs, the statement fails. So you are familiar with this too. The default is WITHOUT VALIDATION and this is why one of our previous statement worked like a charm.
Let’s have a look at our users:
mysql-sql> select * from users; 
+---------------------------------------------------------------------------------------------------------+----------------------------------+------+
| doc                                                                                                     | _id                              | age  |
+---------------------------------------------------------------------------------------------------------+----------------------------------+------+
| {"_id": "06ab653c0c58e7117611685b359e77d5", "age": "41", "name": "Descamps", "firtname": "Frederic"}    | 06ab653c0c58e7117611685b359e77d5 | 41   |
| {"_id": "9828dd6e0c58e7117611685b359e77d5", "age": "41", "name": "Buytaert", "firtname": "Kris"}        | 9828dd6e0c58e7117611685b359e77d5 | 41   |
| {"_id": "c4f986214e58e711434d685b359e77d5", "age": "kid", "name": "Vanoverbeke", "firtname": "Dimitri"} | c4f986214e58e711434d685b359e77d5 | null |
| {"_id": "f24730610c58e7117611685b359e77d5", "age": "42", "name": "Cottyn", "firtname": "Yvan"}          | f24730610c58e7117611685b359e77d5 | 42   |
+---------------------------------------------------------------------------------------------------------+----------------------------------+------+
Now we can try to add the index:
mysql-sql> alter table users add index age_idx(age);
And we can even add new data having the age not being the expected integer:
mysql-js> collection.add({name: "Gryp", firtname: "Kenny", age: "teenager"}).execute();
As you can see, it requires some extra effort if you want to type JSON attributes in MySQL’s virtual columns but this allows you to mix both worlds, NoSQL and SQL, very easily using one single platform !










                                                                                                                                        -As per Report








Comments

Post a Comment

Popular posts from this blog

Pay Per Click by AppWorks Technologies Pvt Ltd

Post a Comment
Read more

Microsoft launches a new AI startup program at Station F in Paris

Microsoft is rethinking its strategy when it comes to startup acceleration in Paris. The company is going to focus on artificial intelligence. This will lead to a new program for AI startups at  Station F . Microsoft has had a startup accelerator in the Sentier neighborhood for a few years now. When Station F opens at the  end of June , the company is going to focus exclusively on artificial intelligence with a partnership with  INRIA  and move everything to the startup campus. “We think that we’re first going to select 5 or 6 startups that can foster an ecosystem around INRIA and themselves,” Microsoft France Developer Experience leader Christophe Shaw told me. “The idea is that we’re eventually going to have a hundred startups in this club.” And the first startup joining this program is  Recast.ai . This French startup has been building a service that helps you build, launch and manage chatbots. The company also takes care of hosting those bots. A few big French companies hav
Post a Comment
Read more

Making telescopes that curve and twist

New computational tool automates design of telescoping structures that provide compact storage and rapid deployment. Shown here: a complex telescoping lizard expands to many times its original volume, serving as a stress test of the method. Credit: Chris Yu/Carnegie Mellon University A new tool for computational design allows users to turn any 3D shape into a collapsible telescoping structure. New mathematical methods developed by researchers at Carnegie Mellon University capture the complex and diverse properties of such structures, which are valuable for a variety of applications in 3D fabrication and robotics—particularly where mechanisms must be compact in size and easily deployable. The research, "Computational Design of Telescoping Structures," led by Carnegie Mellon Professors Stelian Coros and Keenan Crane and PhD student Christopher Yu, will be presented at the annual SIGGRAPH conference, 30 July to 3 August in Los Angeles. The conference each year spotlights the
Post a Comment
Read more