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

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

Reprint my blogThe snake said