MySQL 5.7 New JSON Functionality

Beginning with MySQL 5.7.8, MySQL supports a native JSON type.

• JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements.

• JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error.

• JSON documents are normalized on creation, and can be compared using most comparison operators such as =, <, <=, >, >=, <>, !=, and <=>. Information about supported operators as well as precedence and other rules that MySQL follows when comparing JSON values can be found on the Comparison and Ordering of JSON Values page.

MySQL 5.7.8 also introduces a number of functions for working with JSON values.

Creation Functions

• JSON_ARRAY()
• JSON_MERGE()
• JSON_OBJECT()

You can refer here for more information.

Search Functions

• JSON_CONTAINS()
• JSON_CONTAINS_PATH()
• JSON_EXTRACT()
• JSON_KEYS()
• JSON_SEARCH()

In MySQL 5.7.9 and later, you can use column->path as shorthand for JSON_EXTRACT(column, path). This works as an alias for a column wherever a column identifier can occur in an SQL statement, including WHERE, ORDER BY, and GROUP BY clauses. This includes SELECT, UPDATE, DELETE, CREATE TABLE, and other SQL statements. The left hand side must be a JSON column identifier (and not an alias). The right hand side is a quoted JSON path expression which is evaluated against the JSON document returned as the column value.

You can refer here for more information.

Modification Functions

• JSON_APPEND()
• JSON_ARRAY_APPEND()
• JSON_ARRAY_INSERT()
• JSON_INSERT()
• JSON_QUOTE()
• JSON_REMOVE()
• JSON_REPLACE()
• JSON_SET()
• JSON_UNQUOTE()

You can refer here for more information.

Information Functions

• JSON_DEPTH()
• JSON_LENGTH()
• JSON_TYPE()
• JSON_VALID()

You can refer here for more information.