Postgres 的 JSON / JSONB 类型
从 MySQL 5.7.8 开始，MySQL 支持原生的 JSON 数据类型。
一、介绍
json 是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。
而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。
总结：
| . | json | jsonb |
|---|---|---|
| 存 | 快 | 慢 |
| 取 | 慢 | 快 |
推荐：使用 jsonb，且 jsonb 支持索引，和更多的运算符/函数
二、实践
1、定义
这里我们定义了姓名和毕业院校两个字段
CREATE TABLE "Students"
(
name VARCHAR(255),
edu_experience JSONB
)
2、插入
（1）Postgres SQL
INSERT INTO "Students"
("edu_experience")
VALUES
(
'{"name":"清华大学","year":{"type":"C.E.","value":2002},"remark":["985","211","一本"]}'
)
（2）Sequelize
直接传 JSON 就好
3、取
第一种：直接取
{"name": "清华大学", "year": {"type": "C.E.", "value": 2002}, "remark": ["985", "211", "一本"]}
第二种：深入取
（1）Postgres SQL
-- 方法一
-> 取 json 对象
SELECT "edu_experience"->'name' from "MemberTest" where "id" = 20
-- "清华大学"
->> 取 text
SELECT "edu_experience"->>'name' from "MemberTest" where "id" = 20
-- 清华大学
-> + ->> 取 text
SELECT "edu_experience"->'year'->>'value' from "MemberTest" where "id" = 20
-- 2002
补充：取数组中元素
SELECT "edu_experience"->'remark'->>2 from "MemberTest" where "id" = 20
-- 一本
-- 方法二
#> 取 json 对象
SELECT "edu_experience"#>'{year,type}' from "MemberTest" where "id" = 20
-- "C.E."
#>> 取 text
SELECT "edu_experience"#>>'{year,type}' from "MemberTest" where "id" = 20
-- C.E.
补充：取数组中元素
SELECT "edu_experience"#>>'{remark,2}' from "MemberTest" where "id" = 20
-- 一本
推荐 #> 、#>> 的写法，更简洁一些。
（2）Sequelize
await models.Student.findOne({
attributes: [[models.sequelize.json("edu_experience.name"), "edu_exp_name"]]
})
return:
{
"edu_exp_name": "清华大学"
}
注：
attributes: [models.sequelize.json("edu_experience.name")]这种写法是不对的，必须给取出来的值 AS 重命名下。
4、查询
（1）Postgres SQL
跟上面 3、取 差不多，不赘述了。
（2）Sequelize
where: {
"getEntBasicInfo.domain": "批发业",
},
5、修改
（1）Postgres SQL
一、更新 json
-- 表层值
SELECT jsonb_set ( '{"name": "Jane", "contact": {"fax": "0000","phone": "01234567890"}}' :: jsonb, '{name}', '"colin"');
-- 深入值
SELECT jsonb_set ( '{"name": "Jane", "contact": {"fax": "0000","phone": "01234567890"}}' :: jsonb, '{contact,fax}', '"1111"');
-- 数组中的元素
SELECT jsonb_set ( '{"name": "Jane", "contact": {"fax": ["0000","1111","2222"],"phone": "01234567890"}}' :: jsonb, '{contact,fax,2}', '"1111"');
-- 第四个参数为 TRUE:如果 key 不存在，添加 [默认]
SELECT jsonb_set ( '{"name": "Jane", "contact": {"fax": "0000","phone": "01234567890"}}' :: jsonb, '{name}', '"colin"', TRUE);
-- 第四个参数为 FALSE:如果 key 不存在，不添加
SELECT jsonb_set ( '{"name": "Jane", "contact": {"fax": "0000","phone": "01234567890"}}' :: jsonb, '{age}', '18', FALSE);
二、删除 json
-- 表层值
SELECT '{"name": "James", "email": "james@localhost"}'::jsonb - 'email';
-- 深入值
SELECT '{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}';
可以看到这边用的是上面介绍的 #> 、#>> 的取法
扩展：
1、如果是 json 中有多个值需要更新，如何合并到一句 sql 中？
SET "getEntBasicInfo" =
jsonb_set (
jsonb_set (
jsonb_set (
jsonb_set (
jsonb_set ( "getEntBasicInfo",
'{contactWay,recommendTelephones}', '"推荐电话"' ),
'{contactWay,recommendAddress}', '"推荐地址"' ),
'{basicInfo,domain}', '"行业类别"' ),
'{basicInfo,industryCode}', '"行业代码"' ),
'{basicInfo,industry}','"所属行业"' )
2、如果是 json 中有多个值需要删除，如何合并到一句 sql 中？
-- 表层值
SELECT '{"name": "James", "age": 16, "email": "james@localhost"}' :: jsonb - 'email' - 'name';
-- 深入值
SELECT '{"name": "James", "age": 16, "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}' #- '{age}';
（2）Sequelize
略
