JSON improvements in MySQL 8.0

JSON Improvements in MySQL 8.0
MySQL User Camp
Presented by
S.Pon suresh Pandian
S.Vignesh Prabhu
www.mydbops.com info@mydbops.com 1

About Mydbops
● Founded in 2015, HQ in Bangalore India with 150+ customer base across the globe.
● Mydbops is on Database Consulting with core specialization on MySQL and MongoDB Administration and
Support.
● We have expert team with 20+ certified DBA’s providing full time support and currently managing 300+
servers on premises and cloud.
● We help organisations to architect and scale systems in MySQL/MongoDB by implementing the advanced
technologies in industry which are completely open source.
● We are a leading solution provider in the market for all sort of cloud based database deployments and
management.
2

About Us
3
Pon Suresh Pandian .S
Senior DBA Mydbops
Interested on MySQL Optimization and High Availability systems.
Active MySQL Blogger.
Vignesh Prabhu .S
DBA Mydbops
Interested on MySQL performance and High Availability systems.
Active MySQL Blogger.

Agenda
● JSON INTRODUCTION
● JSON in MySQL
● JSON Functions
● Changes in JSON Functions
● Common JSON functions in MySQL 5.7 & 8.0
● Performance Improvement in 8.0
● Production Use cases
4

JSON Introduction
● Douglas Crockford first introduced the JSON format in 2000.
● Compared to XML, JSON consumes less memory and increases parsing speed.
● JSON is a lightweight format for storing and transporting data.
● JSON is often used when data is sent from a server to a web page.
● JSON was supported by document DB’s in MongoDB(2007), Cassandra(2008).
5

JSON in MySQL
● MySQL introduced a native JSON data type in MySQL 5.7.8 (2015)
● The server would make sure it was a valid JSON document and then save it in a binary format.
● In MySQL JSON data type, a set of SQL functions is available to enable operations on JSON
values, such as creation, manipulation, and searching.
● The Documents are stored in Objects and Array format.
6

JSON Functions
8
Manipulation Formating Searching Stats
Collection
● JSON_INSERT()
● JSON REPLACE()
● JSON_MOVE()
● JSON_SET
● JSON_ARRAY()
● JSON_OBJECT()
● JSON_PRETTY()
● JSON_TABLE
● JSON_SEARCH()
● JSON EXTRACT()
● JSON_LENGTH()
● JSON_TYPE()
● JSON_STORAG
E_SIZE()

Changes in JSON Functions
9
MySQL 5.7 MySQL 8.0
JSON_APPEND() (deprecated 5.7.9) JSON_TABLE()
JSON_MERGE() (deprecated 5.7.22) JSON_STORAGE_FREE()
JSON_ARRAYAGG()
JSON_OBJECTAGG()

Common JSON Functions in 5.7 & 8.0
● JSON_OBJECT()
● JSON_ARRAY()
● JSON_MERGE()
● JSON_PRETTY()
● JSON_TYPE()
● JSON_STORAGE_SIZE()
10
● JSON_EXTRACT()
● JSON_REPLACE()
● JSON_REMOVE()
● JSON_INSERT()
● JSON_SEARCH()

JSON_OBJECT()
● The JSON object it takes a list of key/value pairs and returns a JSON object containing those
pairs.
Example:
mysql> select json_object(doc->>'$.prod_id',doc->>'$.prod_name') from mydbops_labs_test;
+----------------------------------------------------------------+
| json_object(doc->>'$.prod_id',doc->>'$.prod_name’) |
+----------------------------------------------------------------+
| {"1": "Television"} |
| {"2": "Speaker"} |
| {"3": "Mobile Phone"} |
| {"4": "Keyboard"} |
+----------------------------------------------------------------+
12

JSON_ARRAY()
● JSON_ARRAY() will convert the given elements into the array structured format.
Example:
mysql> select json_array(doc) from mydbops_labs_test;
+-------------------------------------------------+
| json_array(json_text) |
+-------------------------------------------------+
| [{"prod_id": "1", "prod_name": "Television"}] |
| [{"prod_id": "2", "prod_name": "Speaker"}] |
| [{"prod_id": "3", "prod_name": "Mobile Phone"}] |
| [{"prod_id": "4", "prod_name": "Keyboard"}] |
+-------------------------------------------------+
● By default, JSON will deliver the result in the Array format only.
13

JSON_MERGE()
● Merge the Array and Object contents and returns the output in array format.
● It’s simply like concat operation.
● Json_merge() = [ array_elements + {object} ].
● This function is removed in MySQL 8.0.3
mysql> select doc1, doc, json_merge(doc1,doc) from mydbops_labs_test1;
+-----------------------+-----------------------------------------------+---------------------------------------------------------------------+
| doc1 | doc | json_merge(doc1,doc) |
| | | |
+-----------------------+-----------------------------------------------+---------------------------------------------------------------------+
| [1, "First Product"] | {"prod_id": "1", "prod_name": "Television"} | [1, "First Product", {"prod_id": "1", "prod_name": "Television"}] |
| [2, "Second Product"] | {"prod_id": "2", "prod_name": "Speaker"} | [2, "Second Product", {"prod_id": "2", "prod_name": "Speaker"}] |
| [3, "Third Product"] | {"prod_id": "3", "prod_name": "Mobile Phone"} | [3, "Third Product", {"prod_id": "3", "prod_name": "Mobile Phone"}] |
| [4, "Fourth Product"] | {"prod_id": "4", "prod_name": "Keyboard"} | [4, "Fourth Product", {"prod_id": "4", "prod_name": "Keyboard"}] |
+-----------------------+-----------------------------------------------+---------------------------------------------------------------------+
14

JSON_PRETTY()
● Displays the Json values in pretty format. Easy to read
mysql> select json_pretty(doc) from mydbops_test limit 2;
+---------------------------------------------------------------------------------------+
| json_pretty(doc) |
+---------------------------------------------------------------------------------------+
| [
1,
"First Product",
{
"prod_id": "1",
"prod_name": "Television"
}
] |
| [
2,
"Second Product",
{
"prod_id": "2",
"prod_name": "Speaker"
}
] |
+---------------------------------------------------------------------------------------+
15

JSON_REPLACE()
● Replace data at multiple places in the document if required. To do this, simply provide multiple
path/value pairs as required.
mysql> update mydbops_labs_test1 set doc= json_replace(doc,'$.prod_name','Air conditioner') where
json_unquote(json_extract(doc1,'$[1]'))='Third Product';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mydbops_labs_test1;
+--------------------------------------------------+------+-----------------------+------------+
| doc | gen | doc1 | order_name |
+--------------------------------------------------+------+-----------------------+------------+
| {"prod_id": "1", "prod_name": "Television"} | 1 | [1, "First Product"] | Sample |
| {"prod_id": "2", "prod_name": "Speaker"} | 2 | [2, "Second Product"] | Sample |
| {"prod_id": "3", "prod_name": "Air conditioner"} | 3 | [3, "Third Product"] | Sample |
| {"prod_id": "4", "prod_name": "Bluetooth"} | 4 | [4, "Fourth Product"] | Sample |
+--------------------------------------------------+------+-----------------------+------------+
19

JSON_REMOVE()
● Remove data at multiple places in the document if required. To do this, simply provide multiple
mysql> update mydbops_labs_test1 set doc= json_remove(doc,'$[0].prod_name') where
json_unquote(json_extract(doc1,'$[1]'))='Fourth Product';
mysql>
+--------------------------------------------------+------+-----------------------+------------+
+--------------------------------------------------+------+-----------------------+------------+
| {"prod_id": "4"} | 4 | [4, "Fourth Product"] | Sample |
+--------------------------------------------------+------+-----------------------+------------+
20

JSON_INSERT()
● Insert data at multiple places in the document if required. To do this, simply provide multiple
mysql> update mydbops_labs_test1 set doc= json_insert(doc,'$.prod_name','Bluetooth','$.prod_test','Test') where
json_unquote(json_extract(doc1,'$[1]'))='Fourth Product';
+-----------------------------------------------------------------+------+-----------------------+------------+
+-----------------------------------------------------------------+------+-----------------------+------------+
| {"prod_id": "4", "prod_name": "Bluetooth", "prod_test": "Test"} | 4 | [4, "Fourth Product"] | Sample |
+-----------------------------------------------------------------+------+-----------------------+------------+
21

JSON_EXTRACT()
● Used to extract the particular elements from the Json document.
ARRAY:
mysql> select doc1,json_extract(doc1,'$[1]') from mydbops_labs_test1 limit 2;
+-----------------------+---------------------------------+
| doc1 | json_extract(doc1,'$[1]') |
+-----------------------+---------------------------------+
| [1, "First Product"] | "First Product" |
| [2, "Second Product"] | "Second Product" |
+-----------------------+---------------------------------+
OBJECT:
mysql> select doc,json_extract(doc,'$.prod_name') from mydbops_labs_test1 limit 2;
+---------------------------------------------+---------------------------------------+
| doc | json_extract(doc,'$.prod_name') |
+---------------------------------------------+---------------------------------------+
| {"prod_id": "1", "prod_name": "Television"} | "Television" |
| {"prod_id": "2", "prod_name": "Speaker"} | "Speaker" |
+---------------------------------------------+---------------------------------------+
23

JSON_SEARCH()
● Returns the position (or) path of the given search pattern.
● Search function has 2 options.
○ ONE
○ ALL
Example:
mysql> select json_search(doc,'ALL','Bluetooth') from mydbops_labs_test1; +-----------------------
-------------------+
| json_search(doc,'ALL','Bluetooth') |
+------------------------------------------+
| NULL |
| NULL |
| NULL |
| ["$.prod_name", "$.prod_test"] |
+------------------------------------------+
24

Performance Improvement in 8.0
1) Faster Update
2) Faster Replication
3) Transform JSON data into relational data
4) Remove Ambiguity
25

Faster Update
● Faster updates, when compared to 5.7
● While using Json functions like json_replace() ( manipulation operations )
MySQL 5.7:
mysql> update jemp set c=json_replace(c,'$.name','mydbops');
Query OK, 1000000 rows affected (30.13 sec)
MySQL 8.0:
mysql> update jemp set c=json_replace(c,'$.name','mydbops');
Query OK, 1000000 rows affected (14.68 sec)
26

Replication use cases
● In MySQL 5.7 update made in JSON document, it will log as a full document in binary log &
replicate the same into slave.
Config settings,
● log-bin
● Binlog_format = ROW
● Binlog_row_image = minimal
27

Replication use cases
In MySQL 8.0 new variable called (binlog_row_value_options)
Binlog settings :
● binlog_row_value_options=partial_json
EXPLAIN FORMAT=JSON should tell if an UPDATE statement will attempt to perform partial update,
and which columns are eligible for partial update.
28

EXPLAIN Plan and Partial Update
mysql> explain format=json update mydbops_labs_test set
doc1=json_replace(json_text,'$.prod_name','mydbops'),doc=json_replace(doc,'$[1]','mydbops')
,s_no=5G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"update": true,
"table_name": "mydbops_labs_test",
"access_type": "ALL",
"rows_examined_per_scan": 4,
"filtered": "100.00",
"partial_update_columns": [
"doc1",
"doc"
]
}
}
}
1 row in set, 1 warning (0.00 sec)
29

Replication use case
INSERT:
mysql> insert into mydbops values(1,'{"tag": "desk1 addr1 history1 grade1",
"book": "table1 and emp_historicaldata", "emp_id": "A1340", "designation":
"MySQL DBA", "position_grade": "A1 grade1 and MASTER in MySQL"}');
UPDATE:
mysql > update mydbops set emp_details =
json_replace(emp_details,'$.emp_id','A1990');
30

Without Partial JSON:
# at 1005
#190209 8:09:15 server id 1 end_log_pos 1233 CRC32 0x9020cc33
Update_rows: table id 112 flags: STMT_END_F
### UPDATE `testing`.`mydbops`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### SET
###
@2='x00x05x00<B5>x00x27x00x03x00*x00x04x00.x00x06x004x00x0bx
00?x00x0ex00x0cMx00x0cix00x0c<87>x00x0c<8D>x00x0c<97>x00tagbooke
mp_iddesignationposition_gradex1bdesk1 addr1 history1 grade1x1dtable1 and
emp_historicaldatax05A1990x09MySQL DBAx1dA1 grade1 and MASTER in MySQL' /*
JSON meta=4 nullable=1 is_null=0 */
31

With Partial JSON:
# mysqlbinlog -vvv --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 > log_02
# at 1081
#190209 8:27:02 server id 2 end_log_pos 1147 CRC32 0x08e02f55
Update_rows_partial: table id 69 flags: STMT_END_F
### UPDATE `testing`.`mydbops`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @2=JSON_REPLACE(@2, '$.emp_id', 'A1990') /* JSON meta=4 nullable=1
is_null=0 */
# at 1147
#190209 8:27:02 server id 2 end_log_pos 1178 CRC32 0xaf9dc5b7 Xid =
14
COMMIT/*!*/;
32

Benefits :
● One of the major advantage is less disk usage.
Reduce disk (IO) :
● By avoiding to logging the full document, we can save the disk usage (IO).
Note :
● More information can be found in our blog on Partial Json updates
(Mydbops)
33

Transform JSON data into relational data
● In MySQL 8.0 introduce new function called JSON_TABLE.
● This JSON_TABLE will convert a JSON document into a relational table.
Example :
mysql > select name,Designation from json_check,json_table(c,'$' columns(name varchar(20)
path '$.name',Designation varchar(20) path '$.Designation')) as a limit 3;
+-------+-------------+
| name | Designation |
+-------+-------------+
| John | DBA |
| Chris | Developer |
| Tom | QA |
+-------+-------------+
35

Remove Ambiguity
● The json_merge() function is deprecated in MySQL 8.0 to remove ambiguity for the merge
operation.
● The json_merge_patch() removes any member in the first object with a matching key in the
second object.
Example :
mysql> select JSON_Merge_patch('{ "a": 1, "b":2}','{ "a": 3, "c":4 }','{"a":5}') as
remove;
+--------------------------+
| remove |
+--------------------------+
| {"a": 5, "b": 2, "c": 4} |
+--------------------------+
36

Production Use Cases
Current Scenario :
● One master slave setup with 3 node document store cluster.
● Storing 80 % data in mysql 5.6 and 20 % data storing in 3 node document store cluster.
● Their application architecture also little complex.
● Server maintenance cost also high.
37

Production Use Cases
Solution :
We suggested to MySQL 8.0.
Benefits :
● To reducing the server cost.
● To reducing the application level complexity and server maintenance .
38

JSON improvements in MySQL 8.0

More Related Content

What's hot

Similar to JSON improvements in MySQL 8.0

More from Mydbops

Recently uploaded

In this document

JSON improvements in MySQL 8.0