MySQL关于常用JSON字符串操作

官方文档

测试数据

CREATE TABLE `demo`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `remark` longtext NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;

INSERT INTO `demo` VALUES 
(1, 'hello'),
(2, '{\"name\": \"Andy\", \"age\": 12}'),
(3, '{\"name\": \"Andy\", \"age\": 12, \"new name\": \"Bob\"}'),
(4, '{\"name\": \"Andy\", \"age\": 12, \"new name\": \"Bob\", \"score\": {\"english\": 90, \"math\": 100}}'),
(5, '[\"Andy\", \"Bob\", \"Cindy\"]'),
(6, '[{\"name\": \"Andy\", \"age\": 12}, {\"name\": \"Bob\", \"age\": 13}]'),
(7, '[{\"name\": \"Andy\", \"age\": 12}, {\"name\": \"Andy\", \"age\": 13}]');

一、查询函数

1. JSON_EXTRACT

含义：提取指定路径的值
格式：JSON_EXTRACT(字段, 路径)

1.1 对于 JSON_OBJECT，路径格式为 $.KeyName

SELECT JSON_EXTRACT(remark, '$.name') FROM demo WHERE id = 2

字符串的值提取出来后会带双引号，用REPLACE替换掉即可：

SELECT REPLACE(JSON_EXTRACT(remark, '$.name'), '"', '') FROM demo WHERE id = 2

或者用 JSON_UNQUOTE

SELECT JSON_UNQUOTE(JSON_EXTRACT(remark, '$.name')) FROM demo WHERE id = 2

如果路径有空格，必须用双引号括起来

SELECT JSON_EXTRACT(remark, '$."new name"') FROM demo WHERE id = 3

允许多级路径，用.连续调用

SELECT JSON_EXTRACT(remark, '$.score.english') AS ENG, JSON_EXTRACT(remark, '$.score.math') AS MATH 
FROM demo WHERE id = 4

可以用通配符*，获取指定路径下的所有值，比如获取根路径的所有值

SELECT JSON_EXTRACT(remark, '$.*') FROM demo WHERE id = 3

同理也可以指定多级路径

SELECT JSON_EXTRACT(remark, '$.score.*') FROM demo WHERE id = 4

1.2 对于 JSON_ARRAY，路径格式为 $[下标].KeyName

获取下标为0的元素

SELECT JSON_EXTRACT(remark, '$[0]') FROM demo WHERE id = 5

获取下标为0的元素的指定key的值

SELECT JSON_EXTRACT(remark, '$[0].age') FROM demo WHERE id = 6

可以使用通配符*，获取所有元素

SELECT JSON_EXTRACT(remark, '$[*].age') FROM demo WHERE id = 6

2. JSON_CONTAINS

含义：查询json文档中指定路径是否包含指定值
格式：JSON_CONTAINS(json字段, 值, 路径)

对于 JSON_OBJECT

SELECT JSON_CONTAINS(remark, '12', '$.age') FROM demo WHERE id = 4

对于 JSON_ARRAY

SELECT JSON_CONTAINS(remark, '12', '$[0].age') FROM demo WHERE id = 6

3. JSON_CONTAINS_PATH

含义：查询json文档中是否包含指定路径
格式：JSON_CONTAINS_PATH(json字段, one / all, 路径)。one表示一个存在即可，all表示所有都存在才满足

SELECT JSON_CONTAINS_PATH(remark, 'one', '$.name') FROM demo WHERE id = 4

4. JSON_SEARCH

含义：返回查找的值的所有路径
格式：JSON_SEARCH(json字段, one / all, 查找值)。one表示一个存在即可，all表示所有都存在才满足

SELECT JSON_SEARCH(remark, 'one', 'Andy') FROM demo WHERE id = 4

SELECT JSON_SEARCH(remark, 'all', 'Andy') FROM demo WHERE id = 7

二、属性函数

1. JSON_VALID

含义：校验 JSON 合法性
格式：JSON_VALID(val)
返回值：0：不合法，1：合法

SELECT JSON_VALID(remark) FROM demo

2. JSON_LENGTH

含义：返回 JSON 数组或对象的长度
格式：JSON_LENGTH(json_doc[, path])

SELECT JSON_LENGTH(remark) FROM demo WHERE id = 4

SELECT JSON_LENGTH(remark, '$.score') FROM demo WHERE id = 4

3. JSON_TYPE

含义：返回json类型
格式：JSON_TYPE(json_val)
返回值：OBJECT 或 ARRAY

SELECT JSON_TYPE(remark) FROM demo WHERE id = 4

SELECT JSON_TYPE(remark) FROM demo WHERE id = 6