Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to operate the JSON data type in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

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))

In the above statement, we mainly pay attention to the field json_col, and the specified data type is 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 after VALUES. Because double quotation marks are needed to identify the string in the data in json format, the content after 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"]}')

At this point, we insert a json array. Mainly pay attention to the problems 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 want to modify something in the JSON data, how do 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

In this SQL statement, the $match represents the JSON field, through. Index to the variations field, and then add an element through the JSON_ARRAY_APPEND function. Now let's execute the query statement:

SELECT * FROM myjson

The results are as follows:

+-+-+ | 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, please refer to the official link JSON Path Syntax

Create an index

MySQL's JSON format data cannot be indexed directly, but it can be modified to pick out the data to be searched separately, a separate data column, and then key an index on this field. Here 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: 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 pulled out separately into the field g, and then indexed on the field g, the query condition is also on the field g.

String to JSON format

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

SELECT CAST ('[1 opening 2 SELECT CAST 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 read the above Do you have any further understanding of how to manipulate JSON data types in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.