心之所向,皆可身往。

MySQL JSON

CREATE TABLE `JSON` (
`ID`  bigint NOT NULL AUTO_INCREMENT ,
`CONTENT`  json NULL DEFAULT NULL ,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
ROW_FORMAT=DYNAMIC
;


SELECT * FROM JSON;
INSERT INTO JSON (CONTENT) VALUES('{"City": "ShenZhen", "Description": "Best damn city in the world"}');
# 使用 字段->'$.json属性' 进行查询条件
SELECT * FROM JSON WHERE `CONTENT` -> '$.City' = 'ShenZhen';
# 使用 json_extract 函数查询,json_extract(字段, "$.json属性")
SELECT * FROM JSON WHERE JSON_CONTAINS(`CONTENT`, JSON_OBJECT('City', "ShenZhen"));
# 根据json数组查询,用 JSON_CONTAINS(字段, JSON_OBJECT('json属性', "内容"))
SELECT * FROM JSON WHERE JSON_EXTRACT(CONTENT,'$.City') = 'ShenZhen';

INSERT INTO JSON(CONTENT) VALUES('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');

SELECT CONTENT->>"$.name" AS NAME FROM JSON WHERE ID > 2;
SELECT JSON_UNQUOTE(JSON_EXTRACT(`JSON`.`CONTENT`,'$.name')) AS `NAME` from `JSON` where (`JSON`.`ID` > 2)

# 字符串转JSON格式
SELECT CAST('[1,2,3]' as JSON) ;
SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

# 不存在age键名,则插入
SELECT JSON_INSERT(CONTENT, '$.age', 26) FROM JSON;
# 存在name键名,则忽略
SELECT JSON_INSERT(CONTENT, '$.name', 'Fred') FROM JSON;
# 存在name键名,则进行替换
SELECT JSON_REPLACE(CONTENT, '$.name', 'Fred') FROM JSON;
# 不存在age键名,则忽略
SELECT JSON_REPLACE(CONTENT, '$.age', 26) FROM JSON;
# 存在name键名,则进行替换
SELECT JSON_SET(CONTENT, '$.name', 'Fred') FROM JSON;
# 不存在age键名,则插入
SELECT JSON_MERGE_PATCH(CONTENT, '{"name":"Vicky","age":26}') FROM JSON;
# 合并,如果存在相同键名,则后面的覆盖前面的,如果值是对象,会递归
SELECT JSON_MERGE_PRESERVE(CONTENT, '{"name":"Vicky","age":26}') FROM JSON;
# 如果存在相同键名,则组成新的对象
SELECT JSON_REMOVE(CONTENT, '$.name') FROM JSON;

SELECT * FROM JSON WHERE `OTHER` -> '$[*].dateTime' > '2022-04-09' LIMIT 100
SELECT * FROM JSON WHERE OTHER -> '$' LIKE "%2016%" LIMIT 10;

# 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