MySQL 8.0 Document Store – How to deal with date & time

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_Schema 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 !