As you know, MySQL 8.0 can be used as JSON Document Store to store your documents without being linked to any schema. You can also use CRUD operations to deal with these documents using the MySQL X DevAPI.
Of course in documents, it’s possible to also store temporal attributes like date, time, datetime, …
Let’s see how we can deal with such attributes.
This is an example of a collection of documents with a datetime attribute createdOn
:
As those attributes don’t have a real type, can we use the createdOn
attribute as if it was a real datetime field ?
Let’s try to get all the documents have a created data > '2021-12-02'
:
We can see that the document “dave” has been filtered out. However, we can see that “kenny” and “miguel” are also present… and this is correct as “2021-12-02 01:20:19
” is indeed bigger than “2021-12-02 00:00:00
“.
Let’s then try with another date format : 2021-12-02 23:59:59
:
This is indeed what we are looking for… but can I just format the value of createdOn
to just filter out using a date without the time ?
You can notice that we can use datetime functions on the attribute. However not all functions are always supported in the X DevAPI:
And what about performance ?
Let’s run again this operation:
JS> db.mycol.find("createdOn > '2021-12-02'").fields('name', 'createdOn')
We can find in Performance_Schem
a the statement as it’s executed by MySQL and check its Query Execution Plan (using EXPLAIN
):
SQL> EXPLAIN SELECT JSON_OBJECT('name', JSON_EXTRACT(doc,'$.name'),'createdOn', JSON_EXTRACT(doc,'$.createdOn')) AS doc FROM `docstore`.`mycol` WHERE (JSON_EXTRACT(doc,'$.createdOn') > '2021-12-02') \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100 Extra: Using where
We can notice that a full table scan is performed to execute the query.
But of course, it’s again possible to optimize this by creating an index:
Now if we check again the Query Execution Plan of the query, we can see that the index is used:
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: range possible_keys: createdOn_idx key: createdOn_idx key_len: 6 ref: NULL rows: 3 filtered: 100 Extra: Using where
But sometimes, the MySQL DBA still needs to use the good all SQL: let’s now decide that we want to use this statement and that we don’t want to change it:
JS> db.mycol.find("date(createdOn) > '2021-12-02'").fields('name', 'createdOn')
Could we optimize it ? Because this CRUD operation, won’t use the previously created index (due to the date()
function on the attribute).
That’s where the MySQL DBA will prove again all the power she/he has !
The trick here is to create a virtual column manually and then index it:
SQL> ALTER TABLE mycol ADD COLUMN created_on_date date
GENERATED ALWAYS AS
(
date(json_unquote(json_extract(doc
,_utf8mb4'$.createdOn')))
) VIRTUAL;
SQL> ALTER TABLE mycol ADD INDEX createdOn_date_idx(created_on_date);
And now the index will be used:
SQL> EXPLAIN SELECT JSON_OBJECT('name', JSON_EXTRACT(doc,'$.name'),'createdOn', JSON_EXTRACT(doc,'$.createdOn')) AS doc FROM `docstore`.`mycol` WHERE (date(JSON_UNQUOTE(JSON_EXTRACT(doc,'$.createdOn'))) > '2021-12-02')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: range possible_keys: createdOn_date_idx key: createdOn_date_idx key_len: 4 ref: NULL rows: 1 filtered: 100 Extra: Using where
As you can see, it’s easy to use Date & Time data inside JSON documents using the X Dev API with MySQL 8.0 Document Store. If needed, it’s also possible to ask the MySQL DBA to generate functional indexes on virtual columns to speed up some operations.
As you may know, MySQL X Protocol is also available in MySQL Database Service on OCI.
Enjoy MySQL !