Two query methods when the MySQL query field type is json

The table structure is as follows:

id varchar(32) 
info json

data:

id = 1
info = {"age": "18","disname":"Xiao Ming"}

--------------------------------------------

Now I need to get the value of disanme in info. The query method is:

1.

select t.id,JSON_EXTRACT(t.info,'$.disname') as disname from tableName t where 1=1

result:

id = 1, disname = "Xiao Ming"

The disname value found in the above SQL statement contains double quotes. Sometimes we don't need double quotes. In this case, we need to use the following method.

2.

select t.id,t.info ->> '$.disname' as disname from tableName t where 1=1

result:

id = 1 , disname = Xiao Ming

ps: Let's look at the mysql query json field

Create table statement

Create Table 
 CREATE TABLE `test` (
  `id` int(10) ,
  `user` json DEFAULT NULL COMMENT 'User information',
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Inserting Data

It should be noted that single quotes are required outside the JSON data to distinguish

INSERT INTO test (id,USER) VALUES(1,'{"name": "tom", "age": 18, "money": 3000}');
INSERT INTO test (id,USER) VALUES(2,'{"name": "jack", "age": 20, "money": 100}');
INSERT INTO test (id,USER) VALUES(3,'{"name": "tony", "age": 21, "money": 100}');
INSERT INTO test (id,USER) VALUES(4,'{"name": "danny", "age": 21, "money": 20}');
INSERT INTO test (id,USER) VALUES(5,'{"name": "janny", "age": 23, "money": 20}');

The table data is as follows

Query Statement

SELECT id,JSON_EXTRACT(USER,'$.name')FROM test;

Here are the query results

Summarize

The above are two query methods introduced by the editor when the MySQL query field type is json. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!