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]}'

A little more complex example for illustration.
SELECT JSON_MERGE('{"first": 1}','{"second": 3}', '{"first": 2}')
will produce
'{\"first\": [1, 2], \"second\": 3}'

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s