Postgres 的 JSON / JSONB 类型

时间：2023-03-09 03:20:35
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/

相关文章