MySQL operations-detailed explanation of JSON data type operations, mysqljson

MySQL operations-detailed explanation of JSON data type operations, mysqljson

In the previous article, we introduced the detailed description of mysql Data Stored Procedure Parameter instances. Today, let's take a look at the JSON data type related to MySQL operations.

Overview

Mysql has supported data storage and query in the json structure since version 5.7.8. This shows that mysql is constantly learning and adding nosql databases. However, after all, mysql is a relational database. It is awkward to process unstructured data such as json.

Create a table with JSON Fields

First, create a table that contains a json field:

CREATE TABLE table_name (  id INT NOT NULL AUTO_INCREMENT,   json_col JSON,  PRIMARY KEY(id));

The preceding statement focuses on the json_col field. The specified data type is JSON.

Insert a simple JSON data

INSERT INTO  table_name (json_col) VALUES  ('{"City": "Galle", "Description": "Best damn city in the world"}');  

In the preceding SQL statement, pay attention to the part after VALUES. Because the data in json format requires double quotation marks to mark strings, the content after VALUES must be enclosed in single quotation marks.

Insert a complex JSON data

INSERT INTO table(col) VALUES('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}');

Here, we insert a json array. Pay attention to the single quotation marks and double quotation marks.

Modify JSON data

In the previous example, we inserted several pieces of JSON data. But if we want to modify some content in the JSON data, how can we achieve this? For example, if we add an element to the variations array, we can:

UPDATE myjson SET dict=JSON_ARRAY_APPEND(dict,'$.variations','scheveningen') WHERE id = 2;

In this SQL statement, $ matches the JSON field, indexes the. number to the variations field, and adds an element through the JSON_ARRAY_APPEND function. Run the following query statement:

SELECT * FROM myjson

The result is:

+----+-----------------------------------------------------------------------------------------+| id | dict                                          |+---+-----------------------------------------------------------------------------------------+| 2 | {"opening": "Sicilian", "variations": ["pelikan", "dragon", "najdorf", "scheveningen"]} |+----+-----------------------------------------------------------------------------------------+1 row in set (0.00 sec)

For how to obtain JSON data in MySQL, refer to the official link JSON Path Syntax.

Create an index

You cannot directly create indexes for MySQL JSON-format data. However, you can use a different method to separate the data to be searched, create a separate data column, and then press an index on this field. The following is an official example:

mysql> CREATE TABLE jemp (  ->   c JSON,  ->   g INT GENERATED ALWAYS AS (c->"$.id"),  ->   INDEX i (g)  -> );Query OK, 0 rows affected (0.28 sec)mysql> INSERT INTO jemp (c) VALUES   >  ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),   >  ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');Query OK, 4 rows affected (0.04 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> SELECT c->>"$.name" AS name   >   FROM jemp WHERE g > 2;+--------+| name  |+--------+| Barney || Betty |+--------+2 rows in set (0.00 sec)mysql> EXPLAIN SELECT c->>"$.name" AS name   >  FROM jemp WHERE g > 2\G*************************** 1. row ***************************      id: 1 select_type: SIMPLE    table: jemp  partitions: NULL     type: rangepossible_keys: i     key: i   key_len: 5     ref: NULL     rows: 2   filtered: 100.00    Extra: Using where1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row *************************** Level: Note  Code: 1003Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)1 row in set (0.00 sec)

In this example, the id field in the JSON field is extracted separately into the field g, and then indexed on the field g. the query condition is also on the field g.

Convert string to JSON format

Converts a string in json format to the JSON type of MySQL:

SELECT CAST('[1,2,3]' as JSON) ;SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

All mysql json Functions

Name	Description
JSON_APPEND ()	Append data to JSON document
JSON_ARRAY ()	Create JSON array
JSON_ARRAY_APPEND ()	Append data to JSON document
JSON_ARRAY_INSERT ()	Insert into JSON array-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT ().
JSON_CONTAINS ()	Whether JSON document contains specific object at path
JSON_CONTAINS_PATH ()	Whether JSON document contains any data at path
JSON_DEPTH ()	Maximum depth of JSON document
JSON_EXTRACT ()	Return data from JSON document-> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE (JSON_EXTRACT ()).
JSON_INSERT ()	Insert data into JSON document
JSON_KEYS ()	Array of keys from JSON document
JSON_LENGTH ()	Number of elements in JSON document
JSON_MERGE ()	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE ()
JSON_MERGE_PRESERVE ()	Merge JSON documents, preserving duplicate keys
JSON_OBJECT ()	Create JSON object
JSON_QUOTE ()	Quote JSON document
JSON_REMOVE ()	Remove data from JSON document
JSON_REPLACE ()	Replace values in JSON document
JSON_SEARCH ()	Path to value within JSON document
JSON_SET ()	Insert data into JSON document
JSON_TYPE ()	Type of JSON value
JSON_UNQUOTE ()	Unquote JSON value
JSON_VALID ()	Whether JSON value is valid

Summary

The above is all the details about the JSON data type operations for MySQL operations in this article, and I hope to help you. If you are interested, you can continue to refer to this site: Detailed description of mysql Data Storage process parameter instances, brief the differences between Redis and MySQL, and several important MySQL variables, you are welcome to leave a message saying that the editor will reply to you in a timely manner and make modifications to provide better articles and reading experience for the majority of programming hobbies and workers. Several books related to MySQL operations are recommended below for your reference:

MySQL database application from entry-level to proficient (version 2nd) PDF scan version

Http://www.bkjia.com/books/361239.html

MySQL5 authoritative guide (version 3rd) Chinese Version PDF Scan

Http://www.bkjia.com/books/367031.html

Hope everyone can like, more exciting content at: http://www.bkjia.com/