JSON Support

1 .JSON Support An overview on how MySQL supports JSON David Ducos 

2 .What are we going to see? • What is JSON? • Why don’t keep using VARCHAR? • How does it work? Costs perspective • Limitations and things to take into account • Benefits of using MySQL JSON Support 2 

3 . What is JSON? JSON (JavaScript Object Notation) How to think it in mysql? But, it is not just an string: OBJECT: JSON objects {"key": JSON_TYPE } set a=5; ARRAY: JSON arrays [,,] BOOLEAN: The JSON true and false literals set a=‘{“id”:”5”}’; NULL: The JSON null literal String and numbers It is a String! you can use string functions: There are some useful functions: JSON_TYPE() mysql> select doc from person limit 1; JSON_ARRAY() +-------------------------------------------------------------------------------------------------+ | doc | JSON_OBJECT() +-------------------------------------------------------------------------------------------------+ | {"_id": "1", "Name": "01234567890123456789012345678901234567890123456789012345678901234567890"} | JSON_EXTRACT() +-------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) JSON_SEARCH() mysql> select replace(doc,'Name','Another thing') from person limit 1; JSON_MERGE() +----------------------------------------------------------------------------------------------------------+ JSON_REPLACE() | replace(doc,'Name','Another thing') | +----------------------------------------------------------------------------------------------------------+ | {"_id": "1", "Another thing": "01234567890123456789012345678901234567890123456789012345678901234567890"} | +----------------------------------------------------------------------------------------------------------+ At the end, you can play with a string inside 1 row in set (0.00 sec) MySQL with some tools. 3 

4 .Why don’t keep using VARCHAR? • You have the functions which makes your work simpler • You don’t need to check syntaxes as it has Automatic validation • You never know the correct VARCHAR size for a JSON, 100? 200? 8000? • New Structures: several Key-Value per row and (ordered) list of elements and recursive structures • You work with Strings in a fashion way 4 

5 .Let’s see examples 1 mysql> CREATE TABLE person (jdoc JSON); Query OK, 0 rows affected (0.04 sec) mysql> select * from person where JSON_SEARCH(jdoc- >’$.Surname','all','Ruiz' ) is not null; mysql> INSERT INTO person VALUES('{"Name": "David", "Surname": +--------------------------------------------------+ "Ducos"}'); | jdoc | Query OK, 1 row affected (0.01 sec) +--------------------------------------------------+ | {"Name": "Soledad", "Surname": ["Ruiz", "Diaz"]} | mysql> select * from person where jdoc->'$.Surname' = "Ducos"; +--------------------------------------------------+ +---------------------------------------+ 1 row in set (0.00 sec) | jdoc | +---------------------------------------+ mysql> INSERT INTO person VALUES('{"Name": "Bruno", | {"Name": "David", "Surname": "Ducos"} | "Surname": "Diaz"}'); +---------------------------------------+ Query OK, 1 row affected (0.01 sec) 1 row in set (0.00 sec) mysql> select * from person where JSON_SEARCH(jdoc- mysql> INSERT INTO person VALUES('{"Name": "Soledad", >'$.Surname','all','Diaz' ) is not null; "Surname": ["Ruiz","Diaz"]}'); +--------------------------------------------------+ Query OK, 1 row affected (0.00 sec) | jdoc | +--------------------------------------------------+ mysql> select * from person where jdoc->'$.Surname' = "Ruiz"; | {"Name": "Soledad", "Surname": ["Ruiz", "Diaz"]} | Empty set (0.00 sec) | {"Name": "Bruno", "Surname": "Diaz"} | mysql> select * from person where jdoc->'$.Surname' = +--------------------------------------------------+ '["Ruiz", "Diaz"]'; 2 rows in set (0.00 sec) Empty set (0.00 sec) 5 

6 .Let’s see examples 2 mysql> update person set jdoc=JSON_MERGE(jdoc,’{"Name" : "Miriam"}') mysql> update person set jdoc=JSON_REPLACE(jdoc,"$.Name" , "['Miriam','Soledad']") where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and JSON_SEARCH(jdoc- JSON_SEARCH(jdoc->'$.Surname','all','Diaz' ) is not null; >'$.Surname','all','Diaz' ) is not null; Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from person mysql> select * from person where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and where JSON_SEARCH(jdoc->’$.Surname','all','Ruiz' ) is not null and JSON_SEARCH(jdoc- json_search(jdoc->'$.Surname','all','Diaz' ) is not null; >'$.Surname','all','Diaz' ) is not null; +--------------------------------------------------------------+ +---------------------------------------------------------------+ | jdoc | | jdoc | +--------------------------------------------------------------+ +---------------------------------------------------------------+ | {"Name": ["Soledad", "Miriam"], "Surname": ["Ruiz", "Diaz"]} | | {"Name": ["Miriam", "Soledad"], "Surname": ["Ruiz", "Diaz"]} | +--------------------------------------------------------------+ +---------------------------------------------------------------+ 1 row in set (0.00 sec) 1 row in set (0.00 sec) mysql> select jdoc->"$.Surname[0]" from person; +----------------------+ | jdoc->"$.Surname[0]" | +----------------------+ | "Ducos" | | "Ruiz" | | "Diaz" | +----------------------+ 3 rows in set (0.00 sec) mysql> select jdoc->"$.Surname[1]" from person; +----------------------+ | jdoc->"$.Surname[1]" | +----------------------+ | NULL | | "Diaz" | 6 | NULL | +----------------------+ 

7 .How does it work? What are the Costs? • When you insert the JSON is validated • Internal binary format for quick access What are the Costs? • Tested with sysbench for write performance • Local VM • 2 Threads over 4 Tables • 500000 requests 7 

8 .How does it work? What are the Costs? Details of the test • Normal LUA insert line: rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s','%s')",i, k_val, c_val, pad_val)) INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 0, ‘74439754257-89435450632-10029680936-01783388742-41861084122- 02284167466-42765454775-69149408995-51723233043-87650756047’,'68425956890-47076763298-51966610044- 96793431771-00000536786'); • JSON LUA insert line: rs = db_query("INSERT INTO " .. table_name .. " (doc) VALUES " .. string.format("('{\"_id\" : \"%d\", \"k\":\"%d\", \"c\":\"%s\",\"pad\":\"%s\" }')",i, k_val, c_val, pad_val)) INSERT INTO sbtest_json1 (doc) VALUES ('{"_id" : "47483648", "k":"59263672", "c":"24912351166-62227201466-48573979646-46226163775- 05759394754-70094713410-97360717151-61106334505-35565977631-88288836923","pad":"51140030063-90587585668- 02097351492-82961843495-96942957620" }’); 8 

9 .Costs Write Throughput Max Response Time Table Size MB Avg Response Time 300 12500 1.5 700 10000 1.2 225 525 7500 0.9 150 350 5000 0.6 75 175 2500 0.3 0 0 0 0 JSON Normal JSON Normal JSON Normal JSON Normal sbtest1 sbtest2 Writes/sec Average 95% Max Response Time sbtest3 sbtest4 9 

10 .Costs - Migration Add a new column in the current table // Add a new table Adding a new extra JSON empty column increased table size (~28 bytes/row) A table with 1M rows will add 28MB - EMPTY Move the data Similar pt-online-schema-change process which involves triggers 10 

11 .Limitations / Take into account • The size of JSON documents stored in JSON columns is limited to the value of the max_allowed_packet system variable. (While the server manipulates a JSON value internally in memory, it can be larger; the limit applies when the server stores it). Default: 4MB / Max:1GB - Dynamic • Sort keys for JSON values are bound by the value of the max_sort_length system variable, so keys that differ only after the first max_sort_length bytes compare as equal. It is important as we are working with Strings. Default: 1KB / Max: 8MB - Dynamic 11 

12 .Benefits of using MySQL JSON Support • Some of the same reasons as you decide to use MongoDB • Dynamic structure • Faster way to developers to introduce changes • Migration opportunities • Secure and optimized • Not supported structures are now supported • Opportunity to re think solutions for some problems 12 

13 . Q&A MySQL is starting to play with JSON By David Ducos 

14 .Join us at Percona Live When: April 24-27, 2017 Where: Santa Clara, CA, USA The Percona Live Open Source Database Conference is a great event for users of any level using open source database technologies. • Get briefed on the hottest topics • Learn about building and maintaining high-performing deployments • Listen to technical experts and top industry leaders Use promo code “WebinarPL” to save an extra 15% off. Register now and get the early bird rate, but hurry prices go up Jan 8th. https://www.percona.com/live/17/register Sponsorship opportunities available as well: https://www.percona.com/live/17/be-a-sponsor 

15 .DATABASE PERFORMANCE MATTERS