Test environment: MySQL8.0.19
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"}' );
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:
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%';
/* 修改 */ 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:
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 valueSELECT
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;
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]'. 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. 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!SELECT
id,
content -> '$[*].key' AS 'key',
content ->> '$[*].value' AS 'value'
FROM
json_demo
WHERE
id < 3
/* content.value的值中存在like'%34%'的值 */
AND content ->> '$[*].value' like '%34%'
/* content.key的值中有4 */
AND JSON_OVERLAPS(content ->> '$[*].key', '4' );
More operations
Name
Description
JSON_ARRAY()
Create a JSON array
JSON_ARRAY_APPEND()
Append data to a JSON document
##JSON_ARRAY_INSERT( )
Insert into JSON array
JSON_CONTAINS()
Whether the JSON document contains a specific object in the path
JSON_CONTAINS_PATH()
Whether the JSON document contains any data in the path
JSON_DEPTH()
The maximum depth of the JSON document
JSON_EXTRACT()
Return data from JSON document
JSON_INSERT()
Insert data into JSON document
JSON_KEYS()
Key array in JSON document
JSON_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 keys
JSON_OBJECT() Create JSON object
JSON_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() ##JSON_QUOTE()Print JSON document in an easy-to-read format
JSON_REMOVE() Reference JSON document
JSON_REPLACE()Remove data from JSON document
JSON_SCHEMA_VALID() (Introduced in 8.0.17) Replace values in JSON documents
JSON_SCHEMA_VALIDATION_REPORT() (Introduced in 8.0.17) Validate JSON documents against the JSON schema; if the document is validated against the schema, then Returns TRUE/1; otherwise, returns FALSE/0.
JSON_SEARCH()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_SET() will Data inserted into JSON document
JSON_STORAGE_FREE() Free space in binary representation of JSON column value after partial update
JSON_STORAGE_SIZE() The space used to store the binary representation of the JSON document
JSON_TABLE() Returns data from a JSON expression as Relational table
JSON_TYPE() JSON value type
JSON_UNQUOTE() Dereference JSON Value
JSON_VALID() Whether the JSON value is valid Extracts 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
##JSON_VALUE() (introduced in 8.0.21)
Returns true (1) if the first operand matches any element of the JSON array passed as the second operand, false (0) otherwise
MEMBER OF() (8.0.17 Introduced)