2

We previously had PostgreSQL database setup and were using JSONB to hold some of our data in json since we need to keep one of our columns data dynamic in nature based on whatever JSON we could store in it. We could also perform textual search and comparison through the JSON keys and values and it all worked well.

Recently due to some technical reasons our team decided to move to MySQL. So my question is does JSON in MySQL support the search like PostgreSQL does and also how stable JSON in MySQL support is given it got introduced in versions >= MySQL 5.7.8, MySQL.

  • Possible duplicate of [How to search JSON data in mysql?](http://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql) – LuFFy Mar 29 '17 at 06:53

1 Answers1

4

Right from the MySQL Documentation:

JSON support

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 <=>; for information about supported operators as well as precedence and other rules that MySQL follows when comparing JSON values, see Comparison and Ordering of JSON Values.

MySQL 5.7.8 also introduces a number of functions for working with JSON values. These functions include those listed here:

Functions that create JSON values: JSON_ARRAY(), JSON_MERGE(), and JSON_OBJECT(). See Section 12.16.2, “Functions That Create JSON Values”. Functions that search JSON values: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(), and JSON_SEARCH(). See Section 12.16.3, “Functions That Search JSON Values”. Functions that modify JSON values: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET(), and JSON_UNQUOTE(). See Section 12.16.4, “Functions That Modify JSON Values”. Functions that provide information about JSON values: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE(), and JSON_VALID(). See Section 12.16.5, “Functions That Return JSON Value Attributes”. 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.

See Section 12.16.3, “Functions That Search JSON Values”, for more information about -> and JSON_EXTRACT(). For information about JSON path support in MySQL 5.7, see Searching and Modifying JSON Values. See also Secondary Indexes and Virtual Generated Columns.

Also we have been using MySQL and storing data in JSON and it works pretty well as far as performance and scalability is concerned.

You can read more about it on: https://dev.mysql.com/doc/refman/5.7/en/json.html

Shuja Ahmed
  • 752
  • 5
  • 17
  • Thanks for the update. Can you share what was the max size of the JSON data that you have handled so far in your db? –  Mar 29 '17 at 05:37
  • Not sure about exact max size but on average our json data holds around 5-6MB of data per column as we also store images inform of base64code against a key value. – Shuja Ahmed Mar 29 '17 at 05:38
  • Great.Thanks a lot really appreciate the help. –  Mar 29 '17 at 05:45