Of Stack

Detailed Explanation of JSON Data Type Operation of MySQL Operation

  • 2021-09-20 21:41:35
  • OfStack

In the previous article, we introduced the detailed explanation of mysql data stored procedure parameter examples. Today, we will look at the related contents of JSON data type of MySQL operation.

Overview

Since version 5.7. 8, mysql has supported data storage and query of json structure, which shows that mysql is constantly learning and adding some aspects of nosql database. However, mysql is a relational database after all, and it is awkward to deal with the unstructured data of json.

Create a table with 1 JSON field

First, create a table that contains a field in json format: 


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

The statement above focuses on the field json_col, which specifies the data type JSON.

Insert a simple piece of JSON data 


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

The above SQL statement mainly pays attention to the part behind VALUES. Because the data in json format needs double quotation marks to identify strings, the content behind VALUES needs to be wrapped in single quotation marks.

Insert a complex piece of JSON data 


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

In this place, we inserted an json array. Mainly pay attention to the problem of single quotation marks and double quotation marks.

Modify JSON data

In the previous example, we inserted several pieces of JSON data, but if we wanted to modify something in the JSON data, how did we do it? For example, if we add one element to the variations array, we can do this: 


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

In this SQL statement, the $match represents the JSON field, indexed to the variations field by the. number, and then added by one element through the JSON_ARRAY_APPEND function. Now we execute the 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 the method of obtaining JSON data in MySQL, refer to the official link JSON Path Syntax

Create an index

MySQL JSON format data can not be directly created index, but can be modified under 1, the data to be searched out separately, a separate data column, and then in this field on the key 1 index. Here's 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: 0

mysql> 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: range
possible_keys: i
     key: i
   key_len: 5
     ref: NULL
     rows: 2
   filtered: 100.00
    Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
 Level: Note
  Code: 1003
Message: /* 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)

This example is very simple, that is, the id field in the JSON field is singled out as the field g, and then indexed on the field g, and the query condition is also on the field g.

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

Summarize

The above is the article on MySQL operation of the JSON data type operation details of the entire content, I hope to help you. Interested friends can continue to refer to this site: mysql data stored process parameters detailed explanation, briefly describe the difference between Redis and MySQL, several important MySQL variables, etc. If there are shortcomings, welcome to leave a message, this site will reply to everyone in time and modify, and strive to provide better articles and reading experience for the majority of programming hobbies and workers. Here are some books related to MySQL operation for your reference:

MySQL database application from introduction to mastery (2nd edition) PDF scanning edition

https://www.ofstack.com/books/361239.html

MySQL5 Authoritative Guide (3rd Edition) Chinese Version PDF Scan Edition

https://www.ofstack.com/books/367031.html

I hope everyone can like it. More exciting content is at: https://www.ofstack.com/

Related articles: