0

MySQL JSON Type

MySQL has started supporting JSON column data type since MySQL 5.7.8. This post explores this new type and its basic usages.

Requirements

Stuff used in this post.

  • MySQL 5.7.16
    • MySQL Community Server (GPL)
  • MySQL WorkBench 6.3.8 Community

What is JSON?

JSON stands for JavaScript Object Notation. It is a type of data in a specific format. Something akin to XML. In the JavaScript parlance, it is an object literal or an object created declaratively.

Consider the following code snippets. These are normally how we’d create an object in most Object-Oriented Programming languages like Java and Kotlin.

In Java

In Kotlin

Even JavaScript has a similar construct:

But what JavaScript can do that others cannot is this:

Essentially, this type of data is native to JavaScript!

MySQL JSON Type and Document

JSON is a document. Thus, the MySQL documentation refers to it as a JSON Document. Storing it in MYSQL as a JSON column has the following benefits overs JSON-formatted strings

  • Automatic validation of JSON documents stored in¬†JSON¬†columns. Invalid documents produce an error
  • Optimized storage format. JSON documents are stored in some internal format that permits fast read access to its elements

The space requirement for a JSON column is roughly the same as LONGBLOG or LONGTEXT. That’s huge!

Some Caveats

  • The size of any JSON Document actually stored in a JSON column is limited to the value of the max_allowed_packet system variable. Meaning, we cannot store a JSON Document whose size is greater than max_allowed_packet. However, the limitation is not applicable to JSON Document still in the server memory.
  • A JSON column cannot have a default value
  • To operate on a JSON column, we need to use a set of SQL functions made available in MySQL specifically for JSON Documents
  • JSON columns cannot be indexed directly
    • We need to index another non-JSON column generated with a scalar value extracted from the JSON column.

JSON Values

JSON Array

A JSON Array is basically a list. A sample value for such is:

JSON Object

A JSON object is an object literal or a key-value map. For example:

We can also have a JSON Array that contains a JSON Object.

Create a Table with JSON Column

To create a table with a JSON column:

Insert JSON Document

When inserting JSON data, all keys and values must be enclosed in a double-quotes.

We can even use the multiple insert construct. Note here that the second JSON data has an additional key-value pair.

Inserting an invalid JSON Document causes an error.

Error message:

Viewing JSON Documents

Index Key for JSON

So how about indexed keys? Since we cannot create an index on a JSON column, we’ll create a non-JSON field whose value is based on some value in the JSON. Yes, that field will have data extracted from the JSON Document stored in that JSON column.

Now, let’s insert the same data using a slightly different insert statements.

Retrieving the data outputs:

Note that creating a primary key on a JSON column is not supported.

References