Catalog

Preface

1. Problem phenomenon

2. Solution

3. JSON Data query

3.1 General basic query operations

3.2  General function query operation

4. JSON Data addition, update and deletion

---

Preface

After going online yesterday, a... Was found through the system alarm bug, So the emergency rollback operation was carried out , But during this period, some users placed orders , The data had an impact , Therefore, it is necessary to check which orders are affected , And repair the data .

1. Problem phenomenon

because bug Led to the of the order form customer_extra_info Field hasFreightInsurance Mistakenly updated to “ yes ”, Therefore, you need to query the total number of false updates to before rollback “ yes ” The order of , Here's the picture ：

So check the order table customer_extra_info The field type found is JSON Type of

2. Solution

Search information found mysql5.7 A new field format is provided later -json.

Yes JSON Data of type MySQL Provides related query operations .

First give the query SQL, In the introduction MySQL Yes JSON Query operation of type field

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTAINS ( Insurance, JSON_OBJECT ( "value", " yes " ) )

3. JSON Data query

3.1 General basic query operations

1、 Use json Field name ->’$.json attribute ’ Query criteria

SELECT
	id,
	customer_extra_info
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'
	AND customer_extra_info -> '$.maxClaimAmount'=10

The query results are as follows ：

 2、 Association table query

json Fields also support the query of associated tables , Here is only the method of use , Do not show examples . among deptLeaderId and id Namely dept,dept_leader Associated fields in two tables .

SELECT * from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;

3.2  General function query operation

Written here, we all found , We inquired about json It's the whole json data , It doesn't seem so convenient , So if we just want to see json What about a field in ？

This introduces our first function :json_extract( Field name ,json Field name )

Before introducing the usage in detail, we can take a look at the function introduction on the official website ：

  We can see the introduction on the official website json_extract（） This function is very detailed ：Return data from JSON document

from json Return field in

1、 function json_extract（）： from json Return the desired field

usage ：json_extract( Field name ,$.json Field name )
example ：

SELECT
	id,
	json_extract ( customer_extra_info, '$.hasFreightInsurance' ) AS hasFreightInsurance 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

The query results are as follows ：

2、 function JSON_CONTAINS()：JSON Whether the format data contains a specific object in the field  

usage : JSON_CONTAINS(target, candidate[, path])
example ：

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info, JSON_OBJECT ( "maxClaimAmount", 10 ) )

The query results are as follows ：

3、 function JSON_OBJECT(): Convert a list of key value pairs into json object  

For example, we want to query the value of an object

We can see hasFreightInsurance There's another object in , There are also name and value Two attribute fields , So how should we query value= No, what about your order .

usage ：JSON_OBJECT([key, val[, key, val] …])
example ：

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTAINS ( Insurance, JSON_OBJECT ( "value", " no " ) )

The query results are as follows ：

4、 function JSON_ARRAY(): establish JSON Array

​ usage ：JSON_ARRAY([val[, val] …])

example ： We need to check deptName contain 1 The data of

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info -> '$.deptName', JSON_ARRAY ( "1" ) )

The query results are as follows ：

5、 function JSON_TYPE(）： Query a json Field attribute type

usage ：JSON_TYPE(json_val)
Example ： For example, we want to query deptName What are the field properties of

SELECT
	id,
	customer_extra_info -> '$.deptName',
	JSON_TYPE ( customer_extra_info -> '$.deptName' ),
	customer_extra_info -> '$.hasFreightInsurance',
	JSON_TYPE ( customer_extra_info -> '$.hasFreightInsurance' ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

The query results are as follows ：

6、 function JSON_EXTRACT() ： from JSON Document return data  

This is also a function often used in our development

SELECT
	* 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( JSON_EXTRACT ( customer_extra_info, '$.hasFreightInsurance' ), JSON_OBJECT ( "value", " no " ) )

  The query results are as follows ：

7、 function JSON_KEYS() ：JSON Key array in document

  usage ：JSON_KEYS(json_value)

example ： For example, we want to query json All in the format data key

SELECT
	id,
	JSON_KEYS ( customer_extra_info ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

  The query results are as follows ：

4. JSON Data addition, update and deletion

Next 3 Both functions are new data types ：
JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)

1、 function JSON_SET() ： Insert data into JSON In the format , Yes key The replacement , nothing key Then add
This is also a function often used in our development process

usage ：JSON_SET(json_doc, path, val[, path, val] …)

example ： For example, we want to target id=2 Add a new set of data ：newData: New data , modify deptName For the new Department 1
sql The statement is as follows ：

update dept set json_value=JSON_SET('{"deptName": " department 2", "deptId": "2", "deptLeaderId": "4"}','$.deptName',' New Department 1','$.newData',' New data ') WHERE id=2;

select * from dept WHERE id =2

result ：

Be careful ：json_doc If you don't take the previous value of this cell , The previous value will be overwritten by the new value , For example, if we change the updated statement to :

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName',' New Department 1','$.newData',' New data ') WHERE id=2

We can see here json_doc yes {“a”:“1”,“b”:“2”}, In this way, the previous cell values will be overwritten and then added / Overwrite this cell field

result :

2、 function JSON_INSERT(): Insert value （ Go to json Insert new value in , But don't replace old values that already exist ）
usage ：JSON_INSERT(json_doc, path, val[, path, val] …)

  example ：

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', ' New Department 2','$.newData2',' New data 2') 
WHERE id=2

result ：

  We can see that because of json_doc The change overwrites the previous value , Added deptName and newData2.
If we do the following just now sql, Just changed value, We'll see what's inside key The value does not change .
Because this function is only responsible for json Insert new value in , But don't replace old values that already exist .

3、 function JSON_REPLACE（）
usage ：JSON_REPLACE(json_doc, path, val[, path, val] …)

Use cases ：
If we want to update id=2 In the data newData2 The value of is ： Updated data 2

sql The statement is as follows ：

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": " New Department 2", "newData2": " New data 2"}', '$.newData2', ' Updated data 2') WHERE id =2;

select * from dept WHERE id =2

result ：

4、 function JSON_REMOVE() ： from JSON Delete data from document
usage ：JSON_REMOVE(json_doc, path[, path] …)

give an example ： Delete key by a Field of .

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": " New Department 2", "newData2": " Updated data 2"}','$.a') WHERE id =2;

result ：

5、 function JSON_SEARCH() ： Used in json Query and return qualified nodes in the format
This is a very powerful function