Json - Percona

[Pages:15]JSON Support

An overview on how MySQL supports JSON

David Ducos

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

What is JSON?

JSON (JavaScript Object Notation)

How to think it in mysql? set a=5; set a=`{"id":"5"}';

It is a String! you can use string functions:

mysql> select doc from person limit 1;

+-------------------------------------------------------------------------------------------------+

| doc

|

+-------------------------------------------------------------------------------------------------+

| {"_id": "1", "Name": "01234567890123456789012345678901234567890123456789012345678901234567890"} |

+-------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select replace(doc,'Name','Another thing') from person limit 1;

+----------------------------------------------------------------------------------------------------------+

| replace(doc,'Name','Another thing')

|

+----------------------------------------------------------------------------------------------------------+

| {"_id": "1", "Another thing": "01234567890123456789012345678901234567890123456789012345678901234567890"} |

+----------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

But, it is not just an string:

OBJECT: JSON objects {"key": JSON_TYPE } ARRAY: JSON arrays [,,] BOOLEAN: The JSON true and false literals NULL: The JSON null literal String and numbers

There are some useful functions:

JSON_TYPE() JSON_ARRAY() JSON_OBJECT() JSON_EXTRACT() JSON_SEARCH() JSON_MERGE() JSON_REPLACE()

At the end, you can play with a string inside MySQL with some tools.

3

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

Let's see examples 1

mysql> CREATE TABLE person (jdoc JSON); Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO person VALUES('{"Name": "David", "Surname": "Ducos"}'); Query OK, 1 row affected (0.01 sec)

mysql> select * from person where jdoc->'$.Surname' = "Ducos";

+---------------------------------------+

| jdoc

|

+---------------------------------------+

| {"Name": "David", "Surname": "Ducos"} |

+---------------------------------------+

1 row in set (0.00 sec)

mysql> INSERT INTO person VALUES('{"Name": "Soledad", "Surname": ["Ruiz","Diaz"]}'); Query OK, 1 row affected (0.00 sec)

mysql> select * from person where jdoc->'$.Surname' = "Ruiz"; Empty set (0.00 sec)

mysql> select * from person where jdoc->'$.Surname' = '["Ruiz", "Diaz"]'; Empty set (0.00 sec)

mysql> select * from person where JSON_SEARCH(jdoc-

>'$.Surname','all','Ruiz' ) is not null;

+--------------------------------------------------+

| jdoc

|

+--------------------------------------------------+

| {"Name": "Soledad", "Surname": ["Ruiz", "Diaz"]} |

+--------------------------------------------------+

1 row in set (0.00 sec)

mysql> INSERT INTO person VALUES('{"Name": "Bruno", "Surname": "Diaz"}'); Query OK, 1 row affected (0.01 sec)

mysql> select * from person where JSON_SEARCH(jdoc-

>'$.Surname','all','Diaz' ) is not null;

+--------------------------------------------------+

| jdoc

|

+--------------------------------------------------+

| {"Name": "Soledad", "Surname": ["Ruiz", "Diaz"]} |

| {"Name": "Bruno", "Surname": "Diaz"}

|

+--------------------------------------------------+

2 rows in set (0.00 sec)

5

Let's see examples 2

mysql> update person set jdoc=JSON_MERGE(jdoc,'{"Name" : "Miriam"}') where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and JSON_SEARCH(jdoc->'$.Surname','all','Diaz' ) is not null; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> update person set jdoc=JSON_REPLACE(jdoc,"$.Name" , "['Miriam','Soledad']") where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and JSON_SEARCH(jdoc>'$.Surname','all','Diaz' ) is not null; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from person

where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and

json_search(jdoc->'$.Surname','all','Diaz' ) is not null;

+--------------------------------------------------------------+

| jdoc

|

+--------------------------------------------------------------+

| {"Name": ["Soledad", "Miriam"], "Surname": ["Ruiz", "Diaz"]} |

+--------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from person

where JSON_SEARCH(jdoc->'$.Surname','all','Ruiz' ) is not null and JSON_SEARCH(jdoc-

>'$.Surname','all','Diaz' ) is not null;

+---------------------------------------------------------------+

| jdoc

|

+---------------------------------------------------------------+

| {"Name": ["Miriam", "Soledad"], "Surname": ["Ruiz", "Diaz"]} |

+---------------------------------------------------------------+

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

|

+----------------------+

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

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-4186108412202284167466-42765454775-69149408995-51723233043-87650756047','68425956890-47076763298-5196661004496793431771-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-4622616377505759394754-70094713410-97360717151-61106334505-35565977631-88288836923","pad":"51140030063-9058758566802097351492-82961843495-96942957620" }');

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download