MySQL 中的JSON

话不多说，直接上代码

-- 参考：https://www.cnblogs.com/kuangke/p/5607471.html

CREATE DATABASE db_test;
USE db_test;
#建一个简单的含有json列的表
CREATE TABLE db_test.jsonTest (
  id INT AUTO_INCREMENT PRIMARY KEY
  COMMENT 'id',
  a  JSON COMMENT 'json字符串'
);
#为表内添加字段
INSERT INTO db_test.jsonTest (a) VALUES ('{"a": "12", "b": "11", "c": "15"}');
INSERT INTO db_test.jsonTest (a) VALUES ('{"a": "1", "b": "2", "c": "3"}');
#看看表内的数据
SELECT * FROM jsonTest;
#查看json字符串中所有的key
SELECT id,json_keys(a) AS "keys" FROM jsonTest;

#分别查询key对应的值
SELECT id,json_extract(a,'$.a[0]') AS "a" FROM jsonTest;
SELECT id,json_extract(a,'$.b') AS "b" FROM jsonTest;
SELECT id,json_extract(a,'$.c[0][0]') AS "c" FROM jsonTest;
#查询 b 和 c 对应的值
SELECT id,json_extract(a,'$.c[0][0]') AS "c" ,JSON_EXTRACT(A,'$.c[0][0]') AS "b" FROM jsonTest;

#------------------------------------------------------------------------------------------------------------
#建一个简单的含有json列的表
CREATE TABLE db_test.jsonTest2 (
  id INT AUTO_INCREMENT PRIMARY KEY
  COMMENT 'id',
  a  JSON COMMENT 'json字符串'
);
#复杂一些的json
INSERT INTO db_test.jsonTest2 (a) VALUES ('{
  "a": [{"A":"aaa","B":"bbb","C":"ccc"}],
  "b": [{"Q":"QQQ","W":"www","E":"eee"}],
  "c":[{"Z":"zzz","X":"xxx","C":"CCC"}]}');
INSERT INTO db_test.jsonTest2 (a) VALUES ('{
  "a": [{"A":"aaa","B":"bbb","C":"ccc"}],
  "b": [{"Q":"QQQ","W":"www","E":"eee"}],
  "c":[{"Z":"zzz","X":"xxx","C":"CCC"}]}');

#看看表内的数据
SELECT * FROM jsonTest2;
#查看json字符串中所有的key,可看到只有第一层的key分别是a，b,c三个key
SELECT id,json_keys(a) AS "keys" FROM jsonTest2;
#查询key  a 对应的值,可看到key a对应的值
SELECT id,json_extract(a,'$.b') AS "b" FROM jsonTest2;

#继续往里面查,查出来发现E没有对应的值(这当然不对。。)
SELECT id,json_extract(b,'$.E') AS "E" FROM
  (SELECT id,json_extract(a,'$.b') AS "b" FROM jsonTest2) AS aa;

#将 '$.b' 修改为 '$b[0]'发现终于可一查询出e对应的值了，可见还是要书写规范啊。。。。。
SELECT id,json_extract(b,'$.E') AS "E" FROM
  (SELECT id,json_extract(a,'$.b[0]') AS "b" FROM jsonTest2) AS aa;

#删除a列中 的一个key  a
UPDATE db_test.jsonTest2 SET jsonTest2.a =json_remove(a,'$.a');