MySQL JSON data type operation

An overview of the

Mysql has supported jSON-structured data storage and queries since version 5.7.8, which shows that mysql is also learning and adding to its noSQL database strengths. However, mysql is a relational database, and it is difficult to handle unstructured data such as JSON.

Create a table of JSON fields

Start by creating 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)
);Copy the code

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

Insert a simple PIECE of JSON data

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

In the SQL statement above, pay attention to the part after VALUES. In JSON data, double quotation marks are required to identify the string. Therefore, the content after VALUES must be wrapped in single quotation marks.

Insert a piece of complex JSON data

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

Here, we’ve inserted a JSON array. I’m going to focus on single quotes and double quotes.

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 that? 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;Copy the code

In this SQL statement, the $match represents the JSON field, which passes. Index to variations and then add an element via the JSON_ARRAY_APPEND function. Now we execute the query:

SELECT * FROM myjsonCopy the code

The result is:

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

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

Create indexes

MySQL JSON data cannot be indexed directly, but can be modified to separate the data to be searched, a single column of data, and then key an index on that field. Here’s 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)Copy the code

This example is very simple. It is to separate the ID field in the JSON field into field G, and then make index on field G. The query condition is also on field G.

The string is converted to JSON format

Convert json string to MySQL json:

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

All MYSQL JSON functions

Reprint my blogThe snake said