Unlocking MySQL 5.7 JSON: Native Support, Indexing, and Virtual Columns

When comparing MySQL and PostgreSQL JSON support, MariaDB's dynamic column is mentioned but suffers from low query performance, lack of indexing, and limited advantages.

Starting with MySQL 5.7.7, the InnoDB storage engine natively supports the JSON data type, offering three main benefits:

JSON validity checking at the database layer, which BLOB cannot provide.

Improved query performance because the engine can parse JSON without scanning entire strings.

Indexing support via virtual columns, allowing parts of JSON documents to be indexed.

Creating a table with a native JSON column:

create table user (
  uid int auto_increment,
  data json,
  primary key (uid)
) engine=innodb;

Inserting JSON records:

insert into user values (NULL, '{"name":"David","mail":"[email protected]","address":"Shangahai"}');
insert into user values (NULL, '{"name":"Amy","mail":"[email protected]"}');

Attempting to insert invalid JSON triggers an error:

insert into user values (NULL, "test");
-- ERROR 3130 (22032): Invalid JSON text: "Invalid value" at position 2 in value (or column) 'test'.

MySQL 5.7 also provides a set of JSON functions for efficient manipulation, e.g. extracting fields:

select jsn_extract(data, '$.name'), jsn_extract(data, '$.address') from user;

The most exciting feature is the virtual column capability, which lets you generate a column from JSON data and index it using a regular B‑tree index:

ALTER TABLE user ADD user_name varchar(128) GENERATED ALWAYS AS (jsn_extract(data, '$.name')) VIRTUAL;
CREATE INDEX idx_username ON user(user_name);
SELECT user_name FROM user;

Using EXPLAIN shows that the optimizer uses the newly created index for fast lookups:

EXPLAIN SELECT * FROM user WHERE user_name='"Amy"' \G

Overall, MySQL 5.7's native JSON support, combined with virtual columns and indexing, offers a robust solution that challenges PostgreSQL's JSON capabilities and surpasses MariaDB's dynamic column approach.