MySQL operations: JSON data type operations

In the previous article, we introduced the detailed example of MySQL data storage procedure parameters. Today, let's take a look at the relevant content of MySQL operations on the JSON data type.

Overview

Since version 5.7.8, MySQL has supported the storage and query of JSON structured data, which shows that MySQL is also constantly learning and adding the advantages of NoSQL databases. But MySQL is a relational database after all, and it is still awkward when processing unstructured data such as JSON.

Create a table with a 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)
);

In the above statement, pay attention to the json_col field, which specifies the data type as JSON.

Insert a simple JSON data

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

In the above SQL statement, pay attention to the part after VALUES. Since double quotes are required to identify strings in json format data, the content after VALUES needs to be wrapped in single quotes.

Insert a complex JSON data

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

Here, we inserted a json array. The main thing is to pay attention to the problem of single quotes and double quotes.

Modifying JSON Data

In the previous example, we inserted several pieces of JSON data, but if we want to modify a certain content in the JSON data, how can we achieve it? For example, if we add an 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, $ represents the JSON field, the dot is used to index the variations field, and then an element is added using the JSON_ARRAY_APPEND function. Now we execute the query:

SELECT * FROM myjson

The result is:

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

For more information about how to obtain JSON data in MySQL, refer to the official link JSON Path Syntax

Create Index

MySQL's JSON format data cannot be indexed directly, but you can work around it by separating the data to be searched into a separate data column and then creating an index on this field. Here is the 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. It takes the id field in the JSON field and separates it into field g. Then, an index is created on field g, and the query condition is also on field g.

Convert string to JSON format

Convert a string in json format to MySQL's JSON type:

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

All MySQL JSON functions

Summarize

The above is all the content of this article about the detailed explanation of JSON data type operations in MySQL. I hope it will be helpful to everyone. Interested friends can continue to refer to this site: Detailed example of MySQL data storage process parameter, brief description of the difference between Redis and MySQL, several important MySQL variables, etc. If there are any deficiencies, please leave a message and point them out. The editor will reply to you in time and make modifications, and strive to provide better articles and reading experience for the majority of programming enthusiasts and workers. Here are some books related to MySQL operation for your reference:

MySQL Database Application from Beginner to Master (2nd Edition) PDF Scanned Version

https://www.jb51.net/books/361239.html

MySQL5 Definitive Guide (3rd Edition) Chinese version PDF scanned version

https://www.jb51.net/books/367031.html

I hope you like it. For more exciting content, please visit: https://www.jb51.net/