Mysql一使用JSON

从Mysql5.7 开始，支持了JSON的数据类型

创建和数据插入

-- 建表
CREATE TABLE emp_details (
    emp_no INT PRIMARY KEY,
    details json
) 
-- 插入数据
INSERT INTO emp_details (emp_no, details)
VALUES
    (
        1,
        '{"location": "in",
            "phone": "+11800000000",
            "email": "abc@example.com",
            "address" : {
                        "line1": "abc", 
                        "line2": "xyz street", 
                        "city": "Bangalore", 
                        "pin": "560103"
                        }
            }'
    );

检索 JSON

-- 使用 -> 检索
mysql> select emp_no, details->'$.address.pin' pin from emp_details;
+--------+----------+
| emp_no | pin      |
+--------+----------+
|      1 | "560103" |
+--------+----------+
1 row in set (0.05 sec)

-- 使用 --> 检索
mysql> select emp_no, details->>'$.address.pin' pin from emp_details;
+--------+--------+
| emp_no | pin    |
+--------+--------+
|      1 | 560103 |
+--------+--------+
1 row in set (0.00 sec)

优雅的浏览、判断

JSON_PRETTY()-优雅浏览

mysql> select emp_no, json_PRETTY(details) from emp_details;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | json_PRETTY(details)                                                                                                                                                                            |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|      1 | {
  "email": "abc@example.com",
  "phone": "+11800000000",
  "address": {
    "pin": "560103",
    "city": "Bangalore",
    "line1": "abc",
    "line2": "xyz street"
  },
  "location": "in"
} |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查找 where子句中使用 col->>path 来从就按照JSON的某一列

mysql> select * from emp_details  where details->> '$.address.pin'='560130';
Empty set (0.00 sec)

mysql> select * from emp_details  where details->> '$.address.pin'="560103";
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details                                                                                                                                                           |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|      1 | {"email": "abc@example.com", "phone": "+11800000000", "address": {"pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "in"} |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_CONTAINS一判断是否存在

-- 1 表示存在
-- 0 表示不存在
mysql> select JSON_CONTAINS(details->>'$.address.pin', "560103") from emp_details;
+----------------------------------------------------+
| JSON_CONTAINS(details->>'$.address.pin', "560103") |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)

JSON_CONTAINS_PATH一判断JSON中一个Key是否存在

-- 如查询address.line 是否存在
-- one 表示至少应该存在一个键
mysql> select json_contains_path(details, 'one', "$.address.line1") from emp_details;
+-------------------------------------------------------+
| json_contains_path(details, 'one', "$.address.line1") |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
1 row in set (0.10 sec)

-- 检测 address.line1 和 address.line2 是否同时存在
-- 使用 all
mysql> select json_contains_path(details, 'one', "$.address.line1", "$.address.line2")
    -> from emp_details;
+--------------------------------------------------------------------------+
| json_contains_path(details, 'one', "$.address.line1", "$.address.line2") |
+--------------------------------------------------------------------------+
|                                                                        1 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改

JSON_SET()

-- 替换现有值，和普通SQl中的set效果一样
update emp_details set details = json_set(details, "$.address.pin", 
"560100", "$.nickname", "kai") where emp_no = 1;

JSON_INSERT()

-- 插入值，但是不替换
update emp_details set details = json_insert(details, "$.address.line3", "xss") 
where emp_no = 1;

JSON_REPLACE()

-- 仅仅替换现有的值
update emp_details set details = json_replace(details, "$.address.pin", "19960118") 
where emp_no = 1;

删除

JSON_REMOVE()

update emp_details set details=json_remove(details, "$.address.line3")
where emp_no = 1;

其他函数

JSON_KEYS()：获取JSON文档中的所有键

mysql> select json_keys(details) from emp_details where emp_no = 1;
+-------------------------------------------------------+
| json_keys(details)                                    |
+-------------------------------------------------------+
| ["email", "phone", "address", "location", "nickname"] |
+-------------------------------------------------------+
1 row in set (0.09 sec)

JSON_LENGTH()：获取JSON文档中的元素数

mysql> select json_length(details) from emp_details where emp_no = 1;
+----------------------+
| json_length(details) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)