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.

Author

Programster

Stuart is a software developer with a passion for Linux and open source projects.

comments powered by Disqus
We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites. More info.