JSON
["a"] - JSON ARRAY
["a","b"] - JSON ARRAY ( Ordered collection of values )
["a", {"b": 20}] - JSON OBJECT ( Having key, value, Unordered set of name/value pairs )
["a", {"b": [20, 30]}]
JSON column is limited to the value of the max_allowed_packet
You can manipulate the size of JSON using JSON_STORAGE_SIZE() function
MySQL handles strings used in JSON context using the utf8mb4 character set.
Partial Updates of JSON Values
In MySQL 8.0 a partial, in-place update of a JSON column.
-
The column being updated was declared as JSON.
-
Can be partial updated using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE()
-
The input column and target column must be the same column
-
Not for partial updates UPDATE ... SET A = JSON_SET ( B, .. )
-
Not for adding any new elements to the parent object or array.
-
The new value cannot be any larger than the old one.
Functions
ORACLE supports many JSON functions.
Most of them related to create/modify functions as SELECT queries.
-
JSON_EXTRACT - Most used, extract specific value
-
JSON_PRETTY() - Print JSON with format
Test cases
mysql> desc products;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | json | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+Insert
mysql> insert into products(data) values('{"Foods":[{"Food":"Bread","Color":"White"},{"Food":"Noodle","Color":"Yellow"}]}');
Query OK, 1 row affected (0.00 sec)
mysql> insert into products(data) values('{"Foods":[{"Food":"Lagsana"},{"Food":"Ramen"}]}');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)SELECT
SELECT LIST
mysql> select * from products;
+----+-----------------------------------------------------------------------------------------+
| id | data |
+----+-----------------------------------------------------------------------------------------+
| 1 | ["Food", "Color"] |
| 2 | {"Foods": [{"Food": "Bread", "Color": "White"}, {"Food": "Noodle", "Color": "Yellow"}]} |
| 3 | {"Foods": [{"Food": "Lagsana"}, {"Food": "Ramen"}]} |
+----+-----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select id, data->"$.Foods[0]" from products;
+----+-------------------------------------+
| id | data->"$.Foods[0]" |
+----+-------------------------------------+
| 1 | NULL |
| 2 | {"Food": "Bread", "Color": "White"} |
| 3 | {"Food": "Lagsana"} |
+----+-------------------------------------+
3 rows in set (0.00 sec)
mysql> select id, json_extract(data, "$.Foods[0]") from products;
+----+-------------------------------------+
| id | json_extract(data, "$.Foods[0]") |
+----+-------------------------------------+
| 1 | NULL |
| 2 | {"Food": "Bread", "Color": "White"} |
| 3 | {"Food": "Lagsana"} |
+----+-------------------------------------+
3 rows in set (0.00 sec)SELECT WHERE
To select specific rows following JSON_FUNCTIONS can be used.
-
JSON_CONTAINS( target, candidate[, path])
-
JSON_EXTRACT( json_doc, path[, path] ...)
-
column->path : An alias for the JSON_EXTRACT
-
JSON_KEYS(json_doc[, path])
-
MEMBER_OF
But to use condition efficiently from JSON data type, consider creating index on generated columns.
mysql> select id, json_extract(data, "$.Foods[0]") from products
-> where data->"$.Foods[0].Color" = 'White';
+----+-------------------------------------+
| id | json_extract(data, "$.Foods[0]") |
+----+-------------------------------------+
| 2 | {"Food": "Bread", "Color": "White"} |
+----+-------------------------------------+
1 row in set (0.00 sec)
mysql> select id, data from products
-> where json_contains_path(data, 'all','$.Foods') = 1;
+----+-----------------------------------------------------------------------------------------+
| id | data |
+----+-----------------------------------------------------------------------------------------+
| 2 | {"Foods": [{"Food": "Bread", "Color": "White"}, {"Food": "Noodle", "Color": "Yellow"}]} |
| 3 | {"Foods": [{"Food": "Lagsana"}, {"Food": "Ramen"}]} |
+----+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)UPDATE, DELETE
Same as normal DML.
For partial updates, you have to use JSON_SET(), JSON_REPLACE(), or JSON_REMOVE().
JSON functions that sound like DML operations, not related DML.
( Such as JSON_ARRAY_APPEND, JSON_REMOVE ... )
mysql> select id, data from products where id = 1;
+----+-------------------+
| id | data |
+----+-------------------+
| 1 | ["Food", "Color"] |
+----+-------------------+
1 row in set (0.00 sec)
mysql> update products
-> set data = '["Food", "Color", "Price"]'
-> where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select id, data from products where id = 1;
+----+----------------------------+
| id | data |
+----+----------------------------+
| 1 | ["Food", "Color", "Price"] |
+----+----------------------------+
1 row in set (0.00 sec)
INDEXING on JSON
MySQL 8.0 Supports for indexing on generated columns.
mysql> Explain
-> select id, data->>"$.Foods[0].Color[0]" from products
-> where data->"$.Foods[0].Color" = 'White';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | products | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+mysql> alter table products add column color varchar(20) generated always as (data->>"$.Foods[0].Color[0]") stored;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create index ix1_products on products (color);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Explain
-> select id, data->>"$.Foods[0].Color[0]" from products
-> where data->>"$.Foods[0].Color[0]" = 'White';
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | products | NULL | ref | ix1_products | ix1_products | 83 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`products`.`id` AS `id`,json_unquote(json_extract(`test`.`products`.`data`,'$.Foods[0].Color[0]')) AS `data->>"$.Foods[0].Color[0]"` from `test`.`products` where (`test`.`products`.`color` = 'White') |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
