How to create & query JSON data in MySQL?

As of MySQL 5.7.8, MySQL started to support native JSON type which allows strong and querying data from a JSON field more efficient:

Automatic validation of JSON documents.
Optimized storage format.
A set of JSON functions to perform operations on JSON values, such as creation, manipulation, and searching more easy.
A set of spatial functions for operating on GeoJSON values is also available.
Normalization, Merging, and Auto-wrapping of JSON Values.

In this post, we are going to provide some basic information on how to use create and query JSON data in MySQL.

1. Create JSON column

To create a new table that contains a JSON data column:

CREATE TABLE table_name (
    ...
    json_column_name json,
    ... 
);

Note that JSON column can’t have default value and can’t be indexed directly.

Example:

Let’s create a table theaters:

CREATE TABLE theaters (
	id int PRIMARY KEY,
	theaters_location json
);

2. Insert JSON data

Now we insert some data into the table theaters:

INSERT INTO theaters
VALUES (
	1000,
	'{"address":{"street1":"340 W Market","state":"MN","city":"Bloomington"}, "zipcode":"55425"}'
),
(
	1003,
	'{"address":{"street1":"45235 Worth Ave.","state":"MD","city":"California"}, "zipcode":"20619"}'
),
(
	1004,
	'{"address":{"state":"CA","city":"Vacaville","street1":"1621 E Monte Vista Ave"} ,"zipcode":"95688"}'
),
(
	1008,
	'{"address":{"state":"AL","city":"Birmingham","street1":"5072 Pinnacle Sq"}, "zipcode":"35235"}'
),
(
	1002, '{"address":{"state":"AZ","city":"Tempe","street1":"1900 E Rio Salado Pkwy"}, "zipcode":"85281"}'
);

A JSON value can be specified as either:

A JSON array contains a list of values separated by commas and enclosed within [ and ] characters:

["abc", 10, null, true, false]

Or a JSON object contains a set of key-value pairs separated by commas and enclosed within { and } characters:

{"key1": "value", "key2": 10}

As we can see from the example above, theater_location column contains JSON objects with lists of values. Beside, a JSON value can be nested within JSON array elements and JSON object key values.

3. Query data from JSON column

First, try the normal SELECT statement:

SELECT * FROM theaters;

The results will be:

id	theater_location
1000	{“address”: {“city”: “Bloomington”, “state”: “MN”, “street1”: “340 W Market”}, “zipcode”: “55425”}
1002	{“address”: {“city”: “Tempe”, “state”: “AZ”, “street1”: “1900 E Rio Salado Pkwy”}, “zipcode”: “85281”}
1003	{“address”: {“city”: “California”, “state”: “MD”, “street1”: “45235 Worth Ave.”}, “zipcode”: “20619”}
1004	{“address”: {“city”: “Vacaville”, “state”: “CA”, “street1”: “1621 E Monte Vista Ave”}, “zipcode”: “95688”}
1008	{“address”: {“city”: “Birmingham”, “state”: “AL”, “street1”: “5072 Pinnacle Sq”}, “zipcode”: “35235”}

To pull values out of the JSON columns, you use the column path operator (->):

SELECT
	id,
	theater_location -> '$.zipcode' AS zipcode
FROM
	theaters;

You can also use the JSON function JSON_EXTRACT:

SELECT
	id,
	JSON_EXTRACT(theater_location, '$.zipcode') as zipcode
FROM
	theaters;

The results will be:

id	zipcode
1000	“55425”
1002	“85281”
1003	“20619”
1004	“95688”
1008	“35235”

The zipcode values are wrapped by the quote marks, to display the values without quote marks, use inline path operator (->>) as follows:

SELECT
	id,
	theater_location ->> '$.zipcode' AS zipcode
FROM
	theaters;

The results will be:

id	zipcode
1000	55425
1002	85281
1003	20619
1004	95688
1008	35235

To query data from the nested JSON objects:

SELECT
	id,
	theater_location ->> '$.address.city' AS city
FROM
	theaters;

The results will be:

id	city
1000	Bloomington
1002	Tempe
1003	California
1004	Vacaville
1008	Birmingham

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.