Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the application of JSON, the new feature of MySQL, is used. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

JSON usage of MySQL5.7

MySQL supports native JSON data types, and columns cannot set default values

Starting with MySQL 5.7.8, MySQL supports the native JSON format, which has a separate json type for storing data in json format.

The data in JSON format is not stored in the database in string format, but in internal binary format, so that the values in json format can be quickly located.

During insert and update operations, MySQL verifies the JSON type and checks whether the data conforms to the json format. If not, an error is reported.

At the same time, version 5.7.8 provides four JSON-related functions so that you don't have to traverse all the data.

A create: JSON_ARRAY (), JSON_MERGE (), JSON_OBJECT ()

B modify: JSON_APPEND (), JSON_ARRAY_APPEND (), JSON_ARRAY_INSERT (), JSON_INSERT (), JSON_QUOTE (), JSON_REMOVE (), JSON_REPLACE ()

JSON_SET (), and JSON_UNQUOTE ()

C query: JSON_CONTAINS (), JSON_CONTAINS_PATH (), JSON_EXTRACT (), JSON_KEYS (), JSON_SEARCH ().

D attribute: JSON_DEPTH (), JSON_LENGTH (), JSON_TYPE () JSON_VALID ().

CREATE TABLE T1 (id INT PRIMARY KEY AUTO_INCREMENT,jdoc json)

1. Insert JSON

Insert a string in json format, either in the form of an object or an array

INSERT INTO T1 (jdoc) VALUES ('{"key1": "value1", "key2": "value2"}')

INSERT INTO T1 (jdoc) VALUE ('[1Jing 2pm 3])

2. View the inserted JSON data type: json_type ()

SELECT json_type (jdoc) FROM T1

Mysql > select json_type ('"abc"')

+-+

| | json_type ('"abc"') |

+-+

| | STRING |

+-+

Mysql > select * from T1

+-+-

| | id | jdoc |

+-+-

| | 1 | {"key1": "value1", "key2": "value2"} |

| | 2 | [1, 2, 3] |

+-+-

2 rows in set (0.00 sec)

Mysql > select json_type (jdoc) from T1

+-+

| | json_type (jdoc) |

+-+

| | OBJECT |

| | ARRAY |

+-+

Mysql > select json_type ('["a", "b", 1]')

+-+

| | json_type ('["a", "b", 1]') |

+-+

| | ARRAY |

+-+

1 row in set (0.00 sec)

3. Use a special function to store the string in JSON array format, and the json_object () function returns the JSON object according to the key-value pair.

Json_array

Mysql > select json_array ("a", 1je now ())

+-+

| | json_array ("a", 1heroin now ()) |

+-+

| | ["a", 1, "2017-11-02 01 purl 47purl 38.000000"] |

+-+

Json_object

Mysql > select json_object ("key1", 1, "key2", "abc")

+-+

| | json_object ("key1", 1, "key2", "abc") |

+-+

| | {"key1": 1, "key2": "abc"} |

+-+

1 row in set (0.00 sec)

Json_merge (): merges two or more JSON documents into one list object

Mysql > select json_merge ('[a', 1Jing 2Jue 3]','{"key1": "value"}')

+-+

| | json_merge ('["a", 1 ~ 2 ~ 2 ~ 3]','{"key1": "value"}') |

+-+

| | ["a", 1, 2, 3, {"key1": "value"}] |

+-+

If the JSON format of multiple lists will be merged into one list store

Mysql > SELECT JSON_MERGE ('[1,2]','["a", "b"]','[true, false]')

+-- +

| | JSON_MERGE ('[1,2]','["a", "b"]','[true, false]') |

+-- +

| | [1,2, "a", "b", true, false] | |

+-- +

1 row in set (0.00 sec)

If multiple objects have the same key, the values are merged into an array.

4. Customize the JSON value to a variable.

Mysql > set @ a=json_object ("key1", "sharesoe")

Query OK, 0 rows affected (0.00 sec)

Mysql > select @ a

+-+

| | @ a |

+-+

| | {"key1": "sharesoe"} |

+-+

1 row in set (0.00 sec)

5. Query and modify JSON values

Get the value by extracting the key in JSON

Mysql > select json_extract (jdoc,'$.key1'), json_extract (jdoc,'$.key2') from T1

+-+

| | json_extract (jdoc,'$.key1') | json_extract (jdoc,'$.key2') | |

+-+

| | "value1" | "value2" |

+-+

1 row in set (0.00 sec)

The data in the query json is in the form of column- > path, where the object type path represents $.path and the array type is $[index]

Mysql > select id,jdoc- >'$.key1', jdoc- >'$.key2 'from T1

+-- +

| | id | jdoc- >'$.key1'| jdoc- >'$.key2'|

+-- +

| | 1 | "value1" | "value2" |

+-- +

Double quotation marks can be removed with the JSON_UNQUOTE function, or from MySQL 5.7.13 through this operator-> >, which is equivalent to JSON_UNQUOTE.

Mysql > select id,jdoc- > >'$.key1', jdoc- > >'$.key2 'from T1

+-- +

| | id | jdoc- > >'$.key1'| jdoc- > >'$.key2'|

+-- +

| | 1 | value1 | value2 |

+-- +

1 row in set (0.00 sec)

Mysql > select id,json_unquote (jdoc- >'$.key1'), jdoc- >'$.key2 'from T1

+-+-- +-+

| | id | json_unquote (jdoc- >'$.key1') | jdoc- >'$.key2'|

+-+-- +-+

| | 1 | value1 | "value2" |

+-+-- +-+

JSON is used as a condition to search. Because JSON is different from a string, if you compare a string with a JSON field, it will not be equal

Mysql > select * from T1 where jdoc=' {"key1": "value1", "key2": "value2"}'

Empty set (0.00 sec)

You can convert a string to JSON through CAST

Mysql > select * from T1 where jdoc=cast ('{"key1": "value1", "key2": "value2"}'as json)

+-+-

| | id | jdoc |

+-+-

| | 1 | {"key1": "value1", "key2": "value2"} |

+-+-

Query through the elements in JSON, object-type query can also be done through column- > path

Mysql > select * from T1 where jdoc- >'$.key1'= "value1"

+-+-

| | id | jdoc |

+-+-

| | 1 | {"key1": "value1", "key2": "value2"} |

+-+-

It is important to note that element searches in JSON strictly distinguish between variable types, such as integers and strings.

Use the JSON_CONTAINS function, but contrary to the form of column- > path, the second argument of JSON_CONTAINS does not accept integers, if the second bit character is also problematic

Select * from T1 where JSON_CONTAINS (jdoc, "value1", "$.key1")

Update JSON

If it is an entire json update, it is similar to that at insert time. However, if you want to update the elements under JSON, MySQL does not support the form column- > path

JSON_INSERT () inserts a new value, but does not overwrite an existing value

Mysql > update T1 set jdoc=json_insert (jdoc,'$.key1','abcd','$.name','www.sharesoe.com') where id=1

Query OK, 1 row affected (0.11 sec)

Mysql > select * from T1

+-+ +

| | id | jdoc |

+-+ +

| | 1 | {"key1": "value1", "key2": "value2", "name": "www.sharesoe.com"} |

+-+ +

1 row in set (0.00 sec)

JSON_SET () inserts a new value and overwrites the existing value

Mysql > update T1 set jdoc=json_set (jdoc,'$.key1','abcd','$.host','www.sharesoe.com') where id=1

Query OK, 1 row affected (0.39 sec)

Mysql > select * from T1

+-+ +

| | id | jdoc |

+-+ +

| | 1 | {"host": "www.sharesoe.com", "key1": "abcd", "key2": "value2", "name": "www.sharesoe.com"} |

+-+ +

1 row in set (0.00 sec)

JSON_REPLACE () replaces only existing values

Mysql > update T1 set jdoc=json_replace (jdoc,'$.key1','abcd1','$.host1','www.sharesoe.com') where id=1

Query OK, 1 row affected (0.14 sec)

Mysql > select * from T1

+-+ +

| | id | jdoc |

+-+ +

| | 1 | {"host": "www.sharesoe.com", "key1": "abcd1", "key2": "value2", "name": "www.sharesoe.com"} |

+-+ +

1 row in set (0.00 sec)

JSON_REMOVE () deletes the JSON element

Mysql > update T1 set jdoc=json_remove (jdoc,'$.key1','$.host') where id=1

Query OK, 1 row affected (0.28 sec)

Mysql > select * from T1

+-+-

| | id | jdoc |

+-+-

| | 1 | {"key2": "value2", "name": "www.sharesoe.com"} |

+-+-

1 row in set (0.00 sec)

It's a boon for businesses that use MySQL varchar to store json. Avoid large field storage when designing tables.

One is to reduce the impact of unnecessary queries on IO, bandwidth, and memory, and the other is to avoid the ddl time cost caused by the large table size to increase the system risk.

This is the end of the application of JSON, the new feature of MySQL. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.