Introduction: In this tutorial, you will learn how to use MySQL JSON data types and store JSON documents in databases.

Introduction to MySQL JSON data types

Starting with version 5.7.8, MySQL supports native JSON data types. The local JSON data type enables you to store JSON documents more efficiently than the JSON text format in previous versions.

MySQL stores JSON documents in an internal format that allows quick reading of document elements. A structure in JSON binary format allows the server to search for values in JSON documents directly through key or array indexes.

The storage of JSON documents is roughly the same as that of LONGBLOB or LONGTEXT data.

To define a column with a data type of JSON, use the following syntax:

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

Note: JSON columns cannot have default values. In addition, JSON columns cannot be indexed directly. You can create an index on the Generated Column that contains the values extracted from the JSON column. When you query data from a JSON column, the MySQL optimizer looks for a compatible index on the virtual column that matches the JSON expression.

In MySQL 5.7, two types of generated columns are supported, namely Virtual Generated Column and Stored Generated Column. The former only saves the Generated Column in the data dictionary (metadata of the table), and does not persist this column of data to the disk; the latter persists the Generated Column to the disk, instead of calculating it every time it is read. Obviously, the latter stores data that can be calculated from existing data and needs more disk space. Compared with Virtual Column, it has no advantage. Therefore, in MySQL 5.7, the type of Generated Column is not specified, and the default is Virtual Column.

Create the column index as follows:

CREATE TABLE table_name (
    ...
`names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`json_column_name` ->> '$.name') NOT NULL, 
    ... 
);

MySQL JSON data type example

Suppose we have to track visitors and their behavior on the site. Some visitors may just view the page, while others may view the page and buy the product. To store this information, we will create a new table called events.

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);

Each event in the event table has an id that uniquely identifies the event. Events also have fields such as views, purchases, and so on. The visitor column is used to store visitor information.

The properties and browser columns are JSON columns. They are used to store the properties of events and browser properties that visitors use to browse websites.

Let's insert some data into the events table:

INSERT INTO events(event_name, visitor,properties, browser) 
VALUES (
  'pageview', 
   '1',
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
('pageview', 
  '2',
  '{ "page": "/contact" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }'
),
(
  'pageview', 
  '1',
  '{ "page": "/products" }',
  '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
(
  'purchase', 
   '3',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1600, "y": 900 } }'
),
(
  'purchase', 
   '4',
  '{ "amount": 150 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),
(
  'purchase', 
  '4',
  '{ "amount": 500 }',
  '{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }'
);

To extract values from JSON columns, use the column path operator (- >).

SELECT id, browser->'$.name' browser
FROM events;

The query returns the following output:

+----+---------+
| id | browser   |
+----+---------+
|  1 | "Safari"  |
|  2 | "Firefox" |
|  3 | "Safari"  |
|  4 | "Firefox" |
|  5 | "Firefox" |
|  6 | "Chrome"  |
+----+---------+
6 rows in set (0.00 sec)

Note that the data in the browser column is enclosed in quotation marks. To remove quotation marks, use the inline path operator (- >), as follows:

SELECT id, browser->>'$.name' browser
FROM events;

As shown in the following output, the quotes have been removed:

+----+---------+
| id | browser |
+----+---------+
|  1 | Safari  |
|  2 | Firefox |
|  3 | Safari  |
|  4 | Firefox |
|  5 | Firefox |
|  6 | Chrome  |
+----+---------+
6 rows in set (0.00 sec)

To get browser usage, use the following statement:

 
SELECT browser->>'$.name' browser, 
      count(browser)
FROM events
GROUP BY browser->>'$.name';

The output of the query is as follows:

+---------+----------------+
| browser | count(browser) |
+---------+----------------+
| Safari  |              2 |
| Firefox |              3 |
| Chrome  |              1 |
+---------+----------------+
3 rozws in set (0.02 sec)

To calculate the total revenue for visitors, use the following query:

SELECT visitor, SUM(properties->>'$.amount') revenue
FROM events
WHERE properties->>'$.amount' > 0
GROUP BY visitor;

This is the output:

+---------+---------+
| visitor | revenue |
+---------+---------+
| 3       |     200 |
| 4       |     650 |
+---------+---------+
2 rows in set (0.00 sec)

In this tutorial, you learned about MySQL JSON data types and how to use it to store JSON documents in a database.