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

略

参考资料：

---

http://www.postgresqltutorial.com/postgresql-json/