JSON for MariaDB Xpand

Overview

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

For information about the JSON data type, see "JSON Type".

JSON DDL

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

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

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)

Xpand 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: Xpand 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)