Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted January 7, 2016

Working with JSON Data in MySQL 7

By Rob Gravelle

Have you heard the news? As of MySQL 5.7.8, MySQL includes a new JavaScript Object Notation (JSON) data type that enables more efficient access to JSON-encoded data. Whereas you could always store JSON data as a varchar or text, native support provides some distinct advantages such as automatic validation of JSON documents stored in JSON columns as well as storage optimization.  In today’s article, we’ll get better acquainted with the new JSON data type.

No More Data Conversion!

The use of JSON to represent object data has become increasingly prevalent in recent years due to its efficient and minimalist character set and ease of conversion between object representation and serialized format.  The main drawback is that, historically, you could not easily work with the data at the database level.  Therefore your best option was to read the data into memory and convert at the application level.  If you’ve ever tried to search WordPress meta data that is stored as an array, you know the pain of which I speak.  In order to locate a particular name/value pair, you have to fetch all of the data, deserialize it, and then perform a lookup using the application code.  Understandably, this leads to less-than-stellar performance because the technology best suited for querying – the database – is all but left out of the searching operation! 

Conversely, MySQL converts JSON documents stored in JSON columns to an internal format that permits quick read access to document elements. Hence, the server may now read a JSON value stored in this binary format without having to convert it from a text representation.  Moreover, the binary format is structured in such a way as to enable the retrieval of child objects directly by key or array index without reading all values before or after them in the document. 

Inserting JSON Data

You can think of a JSON data table as a self-contained document, but can also treat it as a JSON column within a table that may or may not contain other data types.

To create a JSON data table, declare one column of type JSON.

CREATE TABLE widgets (data JSON);

Then, you can insert the document as follows:

mysql> INSERT INTO widgets VALUES('{"key1": "value1", "key2": "value2"}');

It is also possible to insert multiple rows with one statement:

mysql> INSERT INTO widgets (data) VALUES

('{"series": 1}'), ('{"series": 7}'), ('{"series": 3}'),
('{"series": 4}'), ('{"series": 10}'), ('{"series": 2}'),
('{"series": 6}'), ('{"series": 5}'), ('{"series": 8}'),
('{"series": 11}');

The data is validated so that improperly formatted strings will cause an error:

mysql> INSERT INTO widgets VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text: "Invalid value." at position 6 in value (or column) '[1, 2,'.

Positions for "at position N" in error messages like the one above are zero-based, and provide a rough indication of where the problem in a value occurs.

As mentioned earlier, there is nothing stopping you from mixing in other data columns in your tables.  JSON fields are not indexed, so you might want to associate rows with an auto_increment primary key:

CREATE TABLE widgets (
 widget json NOT NULL,

Perhaps the easiest way to load JSON data is to use the LOAD DATA INFILE statement:

mysql> LOAD DATA INFILE 'widgets.json' INTO TABLE widgets (widget);

Selecting Data from a JSON Document

Querying JSON columns is a little more involved that most other data types because of the wide variety of data types that may be represented by a JSON document.  You can select the data directly as long as you aren’t too picky about what comes back:

mysql> SELECT * FROM widgets LIMIT 3;
| data            |
| {"series": 1}   |
| {"series": 7}   |
| {"series": 3}   |

For most queries, you’ll want to use the new JSON functions that are included within MySQL.  Here are a few of them:

The JSON_TYPE() function expects a JSON argument and attempts to parse it into a JSON value. It returns the value's JSON type if it is valid and produces an error if it fails to do so:

mysql> SELECT JSON_TYPE('["a", "b", 1]');
| JSON_TYPE('["a", "b", 1]') |
| ARRAY                      |

The JSON_TYPE() function expects a JSON argument and attempts to parse it into a JSON value. It returns the value's JSON type if it is valid and produces an error otherwise:

mysql> SELECT JSON_TYPE('"hello"');
| JSON_TYPE('"hello"') |
| STRING               |

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

JSON_EXTRACT() returns data from a JSON document and accepts a JSON string and JSON path expression as arguments.  Path expressions are useful to extract parts of a JSON document.  The dollar sign ($) references the document root while periods (.) denote object levels.  For instance, the following query extracts from a JSON document the value of the member with the “series” key at the root level:

mysql> SELECT JSON_EXTRACT('{"series": 4}', '$.series');
| JSON_EXTRACT('{"series": 4}', '$.series')               |
| 4                                             	  |

Since MySQL 5.7.9 you can use inline JSON path expressions that make JSON data queries more human-readable:

JSON_EXTRACT(col, '$.json_field') 

-- can also be expressed as 


As an alternative to writing JSON values using literal strings, there are functions that combine component elements into a JSON object. JSON_ARRAY() is one such function; it takes a list of values and returns a JSON array containing those values:

mysql> SELECT JSON_ARRAY('a', 1, NOW());
| JSON_ARRAY('a', 1, NOW())              |
| ["a", 1, "2015-07-27 09:43:47.000000"] |

JSON_OBJECT() is another; it takes a list of key/value pairs and returns a JSON object containing those pairs:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
| {"key1": 1, "key2": "abc"}            |

JSON_MERGE() takes two or more JSON objects and returns the combined result:

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
| ["a", 1, {"key": "value"}]                 |

Be careful when storing JSON data in variables.   Although the @widget variable in the following example looks like a JSON value, it is not of the JSON data type. Rather, the result from JSON_OBJECT() is being converted to a string when assigned to the variable:

mysql> SET @widget = JSON_OBJECT('key', 'value');
mysql> SELECT @widget;
| @widget          |
| {"key": "value"} |


It’s been a long time coming, but the JSON data type has finally arrived to MySQL.  There’s more to it than we saw here today, so expect to read more about it soon!

See all articles by Rob Gravelle

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM