Loading

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

含义:返回查找的值的所有路径
格式: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

posted @ 2022-04-22 20:37  Convict  阅读(2205)  评论(0编辑  收藏  举报