前言 

最近上有这样一个问题, 项目上线之后, 上下一个版本的时候, 可能需要升级一部分的配置数据 之类的东西 

而 这部分的数据 有些可能是存放在 json数组 字段里面的, postgresql 里面是 json, jsonb 类型 

关于json, jsonb 的相关基础介绍, 可以参见官方文档 : http://www.postgres.cn/docs/10/datatype-json.html

然后 有这样的需求, 就是 需要更新 json 数组里面的某一个属性为 xx 的元素, 更新这个元素的其他属性 

类似于有一个 List<User> friends, 需要更新 其中 name 为 hx 的User 的 age 为 43 

这种操作, 在网上搜索了一下 似乎相关的资料不是太多啊, 大多数文章介绍的就是 官方文档上面的一些 简单的示例, 没办法 只能自己来踩坑摸索了 

 

以下 sql 使用到的 postgresql 版本如下 

select version(); PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

 

 

参考代码

相关表结构, 以及基础数据准备如下 

## 测试建表语句 CREATE TABLE "public"."user" ( "id" varchar(64) COLLATE "default" NOT NULL, "code" varchar(32) COLLATE "default" DEFAULT ''::character varying, "friends" jsonb, CONSTRAINT "baseapp_bank_pkey" PRIMARY KEY ("id") ) WITH (OIDS=FALSE) ; ALTER TABLE "public"."user" OWNER TO "postgres"; COMMENT ON TABLE "public"."user" IS '用户'; COMMENT ON COLUMN "public"."user"."code" IS '编码'; COMMENT ON COLUMN "public"."user"."friends" IS '朋友列表'; # 添加测试数据 delete from "public"."user"; INSERT INTO "public"."user" ("id", "code", "friends") VALUES ('1', 'dzt', '[{"age": "33", "code": "hx"}, {"age": "85", "code": "doit"}]'); INSERT INTO "public"."user" ("id", "code", "friends") VALUES ('2', 'hx', '[{"age": "36", "code": "dzt"}, {"age": "85", "code": "doit"}]'); INSERT INTO "public"."user" ("id", "code", "friends") VALUES ('3', 'doit', '[{"age": "36", "code": "dzt"}, {"age": "33", "code": "hx"}]');

初始化了三个用户, 这三个用户是彼此的朋友, 一个 dzt, 一个 hx, 一个 doit

 

现在我们想把 friends 这一列的里面的 code 为 hx 的元素, age 更新为 43 

查询更新结果

select u.id, u.code, jsonb_set( friends, concat( '{', (array_position(array(select jsonb_array_elements_text(friends) from "public"."user" where id = u.id), (select jae::text from ( select jsonb_array_elements(friends) as jae from "public"."user" where id = u.id ) as tmp where jae::jsonb->>'code' = 'hx') ) - 1), '}' )::_text, -- '{28}', '{"age": "43", "code": "hx"}'::jsonb ) from "public"."user" as u where 1 = 1 and (array_position(array(select jsonb_array_elements_text(friends) from "public"."user" where id = u.id), (select jae::text from ( select jsonb_array_elements(friends) as jae from "public"."user" where id = u.id ) as tmp where jae::jsonb->>'code' = 'hx') ) - 1) >= 0 ;

 

执行结果如下 

得到了我们期望的结果, 我们期望 更新 dzt, doit 的 User 的 friends 列 并将其中的 code 为 hx 的用户 age 更新为 43 

相信到这里, 对应的 update 语句就更好写了吧 

 

这里面将 array_position 的结果 -1, 是因为 数组下标是以 1 开始的 

 

 

这里使用到的相关基础知识整理如下 

---- common usage -- 1. 更新给定的路径的 数据的值 select id, code, jsonb_set(friends, '{0, hobby}', '"game"', true) from "public"."user" where 1 = 1; -- 2. 将json数组转换为 array select id, code, jsonb_array_elements(friends) from "public"."user" where code = 'dzt'; select id, code, array(select jsonb_array_elements(friends)) from "public"."user" where code = 'dzt'; -- 3. 查询给定的 报表维度 的 name 为给定的 key 的配置信息 select * from ( select id, code, jsonb_array_elements(friends) as jae from "public"."user" where 1 = 1 ) as tmp where jae::jsonb->>'code' = 'hx';

相关执行结果如下 

 

 

引用

postgresql数组操作符与函数 https://blog.csdn.net/pg_hgdb/article/details/79483767 关于pgsql 的json 和jsonb 的数据处理笔记 https://www.cnblogs.com/liuchuanfeng/p/8510270.html postgreSQL查询结果添加一个额外的自增序列 https://blog.csdn.net/camillect/article/details/85207934

postgresql 官方文档相关 8.14. JSON 类型 http://www.postgres.cn/docs/10/datatype-json.html 8.15. 数组 http://www.postgres.cn/docs/10/arrays.html#ARRAYS-ACCESSING 9.15. JSON 函数和操作符 http://www.postgres.cn/docs/10/functions-json.html 9.18. 数组函数和操作符 http://www.postgres.cn/docs/10/functions-array.html