MySQL JSON Data Type: Features, Implementation, and Indexing

This article introduces the JSON data type introduced in MySQL 5.7.7, covering its features, implementation details, and usage. It explains why native JSON support was introduced, including document validity checking, efficient access through built-in functions and path expressions, and performance optimization. The article details MySQL's JSON operation functions and path expression syntax, allowing users to manipulate JSON data at the database level.

The storage structure of JSON in MySQL is explained in depth, showing how JSON documents are stored in three sections: a table of pointers to keys and values, sorted keys for binary search lookup, and values in the same order as their corresponding keys. The article discusses the advantages of this storage approach, including fast read operations through selective deserialization and the ability to create indexes on generated columns that extract scalar values from JSON.

JSON indexing is covered, explaining that while direct indexing on JSON columns isn't supported, users can create virtual columns using path expressions and then index those columns. The article also explains JSON comparison and sorting rules, including the two-level collation system where the first level is based on JSON type priority, and the second level uses type-specific comparison rules.

The article concludes by summarizing the key points about MySQL's JSON support, including its benefits, interface functions, storage structure, indexing capabilities, and comparison/sorting behavior.