mysql的json数据类型使用简介

 

定义

Json Array

 json数组，示例：["abc", 10, null, true, false]

 

Json Object

json对象，示例：{"k1": "value", "k2": 10}

以上两者可嵌套，示例：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]

{"k1": "value", "k2": [10, 20]} 

 

实例 

1.建表（带json列）

CREATE TABLE t1 (jdoc JSON);
--CREATE TABLE t2 (jdoc JSON not null);

2.插入

INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

插入json列，会检查内容是否符合格式，不符合时报错，如下：

INSERT INTO t1 VALUES('[1, 2,'); 
--报错：ERROR 3140 (22032) at line 2: Invalid JSON text:
--"Invalid value." at position 6 in value (or column) '[1, 2,'. 

3.更新

UPDATE t1 SET jdoc = '{"key3": "value3", "key4": "value4"}'; --这里为更新整个字段

4.常用函数

• JSON_VALID

有效返回1，无效返回0，传入NULL返回NULL

SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
|                      1 |
+------------------------+

SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
|                   0 |                     1 |
+---------------------+-----------------------+

• JSON_EXTRACT 

抽取函数，从json获取对应路径的值

--json数组
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') ; 
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+

--kv对象
SELECT JSON_EXTRACT('{"key1":"value1", "key2":"value2"}','$.key1');
+--------------------------------------------+
| JSON_EXTRACT('{"key1":"value1", "key2":"value2"}','$.key1') |
+--------------------------------------------+
| "value1"                                         |
+--------------------------------------------+

• ->, ->>

->与JSON_EXTRACT()函数作用相同，->>在前者的基础上去除了结果的双引号

insert into t1(jdoc) values ('[10, 20, [30, 40]]');
SELECT jdoc, jdoc->'$[1]' from t1; 
--结果：20

insert into t1(jdoc) values ('{"key1":"value1", "key2":"value2"}');
SELECT jdoc, jdoc->'$.key1' from t1; 
--结果："value1"

SELECT jdoc, jdoc->>'$.key1' from t1; 
--结果：value1

• JSON_REMOVE

从json数据内移除对应路径的值

SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"]              |
+-------------------------+

SET @j = '{"key1":"value1", "key2":"value2"}';
SELECT JSON_REMOVE(@j, '$.key1');
+-------------------------+
| JSON_REMOVE(@j, '$.key1') |
+-------------------------+
| {"key2":"value2"}         |
+-------------------------+