ClustrixDB now provides Beta support for the JSON data type, including for SQL, many functions, and indexing.

JSON DDL

ClustrixDB supports the JSON data type, similar to the support included in MySQL 5.7. ClustrixDB stores JSON in a native JSON format, which allows for easy retrieval.

sql> create table files (id int primary key auto_increment, doc json);

JSON Functions

ClustrixDB supports the following JSON functions:

Indexing JSON

This example will use the following data and queries:

sql> insert into files (doc) values ('{"foo": {"bar": 1}, "baz": [1,2,3,4]}');
sql> insert into files (doc) values ('{"foo": {"bar": 2}, "baz": [3,4,5]}');

sql> select json_extract(doc, '$.baz') from files where json_extract(doc, '$.foo.bar') = 1;
+----------------------------+
| json_extract(doc, '$.baz') |
+----------------------------+
| [1, 2, 3, 4]               |
+----------------------------+
1 row in set (0.01 sec)

ClustrixDB supports indexing raw JSON column values:

sql> alter table files add column foobar json; 

But also, you can index a JSON attribute by creating a generated column and creating an index on that:

sql> alter table files add column foobar2 json generated always as (json_extract(doc, '$.foo.bar'));
sql> alter table files add index foobar2_id (foobar2);

This generated column + index can now be used to filter for values:

sql> explain select foobar2 from files where foobar2 = 1;
+------------------------------------------------------+-----------+-----------+
| Operation                                            | Est. Cost | Est. Rows |
+------------------------------------------------------+-----------+-----------+
| index_scan 1 := files.foobar2_id, foobar2 = param(0) |      4.61 |      1.01 |
+------------------------------------------------------+-----------+-----------+
1 row in set (0.00 sec)

Note: ClustrixDB can not yet translate expressions in queries to match their equivalent indexed generated column. This SQL statement is equivalent to the one above, but cannot make use of its associated index:

sql> explain select json_extract(doc, '$.baz') from files where json_extract(doc, '$.foo.bar') = 1; 
+-------------------------------------------------------+-----------+-----------+
| Operation                                             | Est. Cost | Est. Rows |
+-------------------------------------------------------+-----------+-----------+
| stream_combine                                        |     13.54 |      0.91 |
|   compute expr0 := json_extract(1.doc, param(0))      |     12.45 |      0.91 |
|     filter (json_extract(1.doc, param(2)) = param(1)) |     12.43 |      0.91 |
|       index_scan 1 := files.__idx_files__PRIMARY      |     12.41 |      1.01 |
+-------------------------------------------------------+-----------+-----------+
4 rows in set (0.01 sec)

Caveats for JSON

ClustrixDB does not support:

Differences from MySQL

Inserting JSON

Querying JSON

Due to some of the differences between ClustrixDB and MySQL, if you are replicating to MySQL using SBR, you may encounter errors.