MySQL 5.7.9 has a new feature, that simplifies queries that deal with JSON data and makes more human-readable: inlined JSON path expressions. Now you can do following:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> CREATE TABLE employees (data JSON); Query OK, 0 rows affected (0,01 sec) mysql> INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}'); Query OK, 1 row affected (0,00 sec) mysql> INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}'); Query OK, 1 row affected (0,00 sec) mysql> SELECT * FROM employees WHERE data->'$.id'= 2; +--------------------------+ | data | +--------------------------+ | {"id": 2, "name": "Joe"} | +--------------------------+ 1 row in set (0,01 sec) |
Let’s take a closer look at the expression in the WHERE clause. It contains an equality condition for the inlined JSON path expression and a constant. The arrow ‘->’ on the right side is the separator. It splits the field on the left side and the path expression on the right of itself. The field could be specified in any way MySQL allows:
- field
- table.field
- db.table.field
The path expression is a string containing valid JSON path expression described in WL#7909. We have been inspired by Facebook’s DocStore project which has an intuitive way to access JSON in SQL. They used a dot notation though e.g. doc.address.zipcode. We have chosen -> as separator will avoid ambiguities.
The whole expression data->'$.id' is a syntactic sugar for json_extract(data, '$.id'). Due to that, it’s treated like json_extract() was specified instead. Here are few examples how it looks like. You can use it in a SELECT:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> EXPLAIN SELECT * FROM employees WHERE data->'$.id'=1; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0,00 sec) mysql> SHOW WARNINGS; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`employees`.`data` AS `data` from `test`.`employees` where (json_extract(`test`.`employees`.`data`,'$.id') = 1) | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec) |
Define a Generated Column using it:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> ALTER TABLE employees ADD COLUMN id INT AS (data->'$.id') STORED NOT NULL, ADD PRIMARY KEY(id); Query OK, 5 rows affected (0,18 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE employees; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | employees | CREATE TABLE `employees` ( `data` json DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `id` int(11) GENERATED ALWAYS AS (json_extract(`data`,'$.id')) STORED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec) |
This way you could have your documents indexed automatically by an id stored inside the document. If you need a string data to be indexed, make sure you’re using the unquoting function — json_unquote() — in order to get index used properly:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> ALTER TABLE employees ADD COLUMN name VARCHAR(10) AS (JSON_UNQUOTE(data->'$.name')) STORED, ADD KEY name_idx(name); Query OK, 2 rows affected (0,20 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employees WHERE name='Joe'; +--------------------------+----+------+ | data | id | name | +--------------------------+----+------+ | {"id": 2, "name": "Joe"} | 2 | Joe | +--------------------------+----+------+ 1 row in set (0,00 sec) mysql> explain SELECT * FROM employees WHERE name='Joe'; +----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | name_idx | name_idx | 13 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0,00 sec) |
Define a view:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> CREATE VIEW v1 AS SELECT data->'$.id', data->'$.name' FROM employees; Query OK, 0 rows affected (0,01 sec) mysql> SELECT * FROM v1; +--------------+----------------+ | data->'$.id' | data->'$.name' | +--------------+----------------+ | 1 | "Jane" | | 2 | "Joe" | +--------------+----------------+ 2 rows in set (0,01 sec) mysql> SHOW CREATE VIEW v1; +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | v1 | CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `v1` AS select json_extract(`employees`.`data`,'$.id') AS `data->'$.id'`,json_extract(`employees`.`data`,'$.name') AS `data->'$.name'` from `employees` | utf8 | utf8_general_ci | +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0,00 sec) |
The inlined path expressions are allowed everywhere, where regular call
to json_extract() function is allowed. For example (but not limited to):
SELECT lists, WHERE/HAVING clauses, ORDER/GROUP BY clauses, etc.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
mysql> SELECT * FROM employees WHERE data->'$.id' IN (1,3,5); +---------------------------+------+ | data | id | +---------------------------+------+ | {"id": 1, "name": "Jane"} | 1 | | {"id": 3, "name": "Bob"} | 3 | | {"id": 5, "name": "Sam"} | 5 | +---------------------------+------+ 3 rows in set, 1 warning (0,00 sec) mysql> CREATE TABLE ids AS SELECT data->'$.id' FROM employees; Query OK, 5 rows affected (0,15 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM ids; +--------------+ | data->'$.id' | +--------------+ | 1 | | 2 | | 3 | | 4 | | 5 | +--------------+ mysql> ALTER TABLE employees ADD COLUMN name VARCHAR(10); Query OK, 0 rows affected (0,17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> UPDATE employees SET name= data->'$.name'; Query OK, 5 rows affected (0,02 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> SELECT * FROM employees; +---------------------------+------+--------+ | data | id | name | +---------------------------+------+--------+ | {"id": 1, "name": "Jane"} | 1 | "Jane" | | {"id": 2, "name": "Joe"} | 2 | "Joe" | | {"id": 3, "name": "Bob"} | 3 | "Bob" | | {"id": 4, "name": "Rob"} | 4 | "Rob" | | {"id": 5, "name": "Sam"} | 5 | "Sam" | +---------------------------+------+--------+ 5 rows in set (0,00 sec) |
The same logic applies to where inlined JSON path expressions can’t be used — where json_extract() can’t be used. For example:
|
1
2
|
mysql> UPDATE employees SET data->'$.name'='Samantha' WHERE data->'$id'=5; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->'$.name'='Samatha' WHERE data->'$id'=5' at line 1 |
Though they’re nice and readable, inlined JSON path expressions aren’t a
complete replacement for the json_extract() function. There are number of
limitations:
| Inlined JSON path expression | json_extract() function | |
|---|---|---|
| Data source | A table's field only | Any JSON-typed value |
| path expression | A plain string only | Any string-typed value |
| Number of path expressions | Only one | Multiple |
If you want to dig further into the actual specification of the inlined JSON path expression, it is described in WL#8607.
Please let us know what you think of this new JSON features! We’d love to hear your feedback on what else you’d like to see related to our wider JSON support. If you encounter any problems with these new features, please let us know here in the comments, open a bug report at bugs.mysql.com, or open a support ticket.
Thank you for using MySQL!