MySQL and JSON

MySQL 5.7.8 now supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: 

 

Document Validation - Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error

Efficient Access - More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is stored in an optimized binary format that allows for quicker access to object members and array elements.

Performance - Improve your query performance by creating indexes on values within the JSON columns. This can be achieved with “functional indexes” on virtual columns. 

Optimized storage format JSON documents stored in JSON columns are converted to an internal format that permits efficient access to document elements.

Convenience - The additional inline syntax for JSON columns makes it very natural to integrate Document queries within your SQL. For example

Along with the JSON data type, a set of SQL functions is available to enable operations on JSON values, such as creation, manipulation, and searching. In addition, the CONVERT() and CAST() functions can convert values between JSON and 

Example 

JSON object

{ "name":"John", "age":30, "car":null } 

Now create a sample table to store the JSON data

CREATE TABLE tb_json
  (
     jdoc JSON
  ); 

Now Insert a record

INSERT INTO `tb_json`
            (`jdoc`)
VALUES      ('{ "name":"John", "age":30, "car":null }' ); 

Now read the data

SELECT *
FROM   `tb_json`  


output

{"age": 30, "car": null, "name": "John"}

Now reading the value of a key inside a JSON using column-path operator(->)

SELECT jdoc -> '$.age'
FROM   `tb_json` 

output
30

References

MySQL 5.7.8