How to use JSON type fields in MySQL

Test environment: MySQL8.0.19

Preparation

CREATE TABLE json_demo ( 
	`id` INT ( 11 ) NOT NULL PRIMARY KEY, 
	`content` json NOT NULL 
);
INSERT INTO json_demo ( id, content )
VALUES
	/*这条是数组*/
	( 1, '[{"key": 1, "order": 1, "value": "34252"},{"key": 2, "order": 2, "value": "23423"}]' ),
	/*这条是数组*/
	( 2, '[{"key": 4, "order": 4, "value": "234"},{"key": 5, "order": 5, "value": "234324523"}]' ),
	/*这条是对象*/
	( 3, '{"key": 3, "order": 3, "value": "43242"}' ),
	/*这条是对象*/
	( 4, '{"key": 6, "order": 6, "value": "5423"}' );

JSON object basic operation

Query the specified field value

/* 基础查询 */
SELECT
	content -> '$.key' AS 'key',
	JSON_EXTRACT(content, '$.key') AS 'key2',
	content -> '$.value' AS 'value',
	JSON_EXTRACT(content, '$.value') AS 'value2',
	content ->> '$.value' AS 'value3',
	JSON_UNQUOTE(JSON_EXTRACT(content, '$.value')) AS 'value4'
FROM
	json_demo 
WHERE
	id > 2;

TIPS:

• ##-> and ->> are the syntax designed by MySQL , among which -> is supported in MySQL5.7, and ->> is supported in MySQL8.0.

• -> Equivalent to JSON_EXTRACT(), when the query field is a string, the return value will also have "".

• ->> Equivalent to JSON_UNQUOTE(JSON_EXTRACT()), when the query field is a string, the return value will not have "".

Used for conditional query

content -> '$.key' can be regarded as a field, and the operations that can be performed on a field are basically He can do it.

SELECT
	id,
	content -> '$.key' AS 'key',
	content ->> '$.value' AS 'value3'
FROM
	json_demo 
WHERE
	id > 2
	AND content -> '$.key' > 1
	AND content -> '$.value' like '%2%';

Modify the specified field value

/* 修改 */
UPDATE json_demo 
SET content = JSON_REPLACE(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* JSON_SET也可以 */
UPDATE json_demo 
SET content = JSON_SET(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* 查询修改结果 */
SELECT id,content,content -> '$.key' AS 'key',content ->> '$.value' AS 'value3'
FROM json_demo WHERE id = 3;
/* 重新赋值 */
UPDATE json_demo SET 
content = JSON_REPLACE(content,'$.key',3,'$.value','43242') WHERE id = 3;

TIPS:

• Both JSON_REPLACE and JSON_SET can be used to modify the value of a certain field. The difference is that when JSON_REPLACE replaces a non-existent attribute, the operation is invalid; while JSON_SET will insert the non-existent attribute.

• So JSON_SET can also be used to append attributes, similar to JSON_INSERT. The difference is that JSON_INSERT operation will fail if an existing attribute is inserted, while JSON_SET will replace it.

Append elements

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.key', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.temp', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_SET(content, '$.temp2', 432)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

##JSON array operation

Query specification Field value

SELECT
	id,
	content -> '$[*].key' AS 'key',
	content ->> '$[*].value' AS 'value',
	content -> '$[0].key' AS 'key2',
	content ->> '$[0].value' AS 'value2',
	/* 查询数组长度 */
	JSON_LENGTH(content) AS 'length'
FROM
	json_demo 
WHERE
	id < 3;

is used for conditional query

SELECT
	id,
	content -> '$[*].key' AS 'key',
	content ->> '$[*].value' AS 'value'
FROM
	json_demo 
WHERE
	id < 3
	/* content.value的值中存在like&#39;%34%&#39;的值 */
	AND content ->> &#39;$[*].value&#39; like &#39;%34%&#39;
	/* content.key的值中有4 */
	AND JSON_OVERLAPS(content ->> &#39;$[*].key&#39;, &#39;4&#39; );

Modify the specified field value

The basic operations are not much different from the JSON object, that is, add the corresponding index bit '$[0]' after '$', and specify all '$[*] '. If the array contains an array, you can specify the deep array elements through '$[1][2][3]'.

Append elements

Both JSON_ARRAY_APPEND and JSON_ARRAY_INSERT can append array elements. The difference is that JSON_ARRAY_APPEND does not need to specify the index bit, in which case it will be appended to the last position; JSON_ARRAY_INSERT must specify the index bit, and an error will be reported if not specified.

JSON_ARRAY_APPEND is appended after the specified index bit, while JSON_ARRAY_INSERT is inserted in front of the specified index bit.

More operations

##JSON_ARRAY_INSERT( )Insert into JSON arrayJSON_CONTAINS()Whether the JSON document contains a specific object in the pathJSON_CONTAINS_PATH()Whether the JSON document contains any data in the pathJSON_DEPTH()The maximum depth of the JSON documentJSON_EXTRACT()Return data from JSON documentJSON_INSERT()Insert data into JSON documentJSON_KEYS()Key array in JSON documentJSON_LENGTH()In JSON document Number of elements JSON_MERGE() (deprecated) Merge JSON documents, retaining duplicate keys. Deprecated synonyms for JSON_MERGE_PRESERVE()JSON_MERGE_PATCH()Merge JSON documents, replacing values ​​for duplicate keys JSON_MERGE_PRESERVE()Merge JSON documents, retaining duplicate keysJSON_OBJECT()Create JSON objectJSON_OVERLAPS() (Introduced in 8.0.17) Compares two JSON documents and returns TRUE (1) if they have any key-value pairs or array elements in common, otherwise returns FALSE (0) JSON_PRETTY()Print JSON document in an easy-to-read format##JSON_QUOTE()JSON_REMOVE()JSON_REPLACE()JSON_SCHEMA_VALID() (Introduced in 8.0.17) JSON_SCHEMA_VALIDATION_REPORT() (Introduced in 8.0.17) JSON_SEARCH()##JSON_SET()will Data inserted into JSON documentJSON_STORAGE_FREE()Free space in binary representation of JSON column value after partial updateJSON_STORAGE_SIZE()The space used to store the binary representation of the JSON document JSON_TABLE() Returns data from a JSON expression as Relational tableJSON_TYPE()JSON value typeJSON_UNQUOTE()Dereference JSON ValueJSON_VALID()Whether the JSON value is validExtracts a value from the JSON document at the location pointed to by the supplied path; returns this value as VARCHAR (512) or the specified type Returns true (1) if the first operand matches any element of the JSON array passed as the second operand, false (0) otherwise

Name	Description
JSON_ARRAY()	Create a JSON array
JSON_ARRAY_APPEND()	Append data to a JSON document
Reference JSON document
Remove data from JSON document
Replace values ​​in JSON documents
Validate JSON documents against the JSON schema; if the document is validated against the schema, then Returns TRUE/1; otherwise, returns FALSE/0.
Validates a JSON document against a JSON schema; returns a report on the validation results in JSON format, including success or failure and failure Reason
The path of the value in the JSON document
	##JSON_VALUE() (introduced in 8.0.21)
	MEMBER OF() (8.0.17 Introduced)

The above is the detailed content of How to use JSON type fields in MySQL. For more information, please follow other related articles on the PHP Chinese website!