JSON and MySQL 5.7

In the past few months I have been inundated with questions about the new JSON data type. The man page for JSON is fascinating reading. So grab your favorite beverage, the JSON manual page, and take a comfy seat for reading. There are many highlights but be sure to check out some of these highlights.

Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error and this is a big advantage over putting JSON data in a string column.

JSON columns can not have a DEFAULT value, can not be indexed (virtual columns for secondary indexes are okay)

MySQL handles strings used in a JSON columnusing the utf8mb4 character set and utf8mb4_bin collation.

One thing to note is that strings are normalized. If you duplicate a key the new value is discarded.SELECT JSON_OBJECT('first', 1, 'second', 2, 'first', 3, 'third', 4);
Will return'{\"first\": 1, \"third\": 4, \"second\": 2}'

The second ‘first’ goes away. So not only does your JSON need to be well formed but you can not reuse keys in a sting.

If you use JSON_MERGE the values for that key are merged into an array.SELECT JSON_MERGE('{"first": 1}','{"first": 2}')
Here First-1 and First-2 are merged into a single array.'{\"first\": [1, 2]}'

And how big ban a JSON doc be? IT can not be stored longer than the max_allowed_packet system variable setting but it can be longer in memory for manipulation. And the protocol limit for max_allowed_packet is one gigabyte. So docs longer than 1GB will need to be saved in 1GB pieces.