pg数据库中读取json

1.需求:获取jason串中name=路段2的value

1.源数据

INSERT INTO "public"."t_param_config"("id", "key", "name", "values") VALUES ('99a93e373ceb43b7bf1e3b30b71b5906', 'roadsect', '所属路段*', '[{"name":"路段1","value":"LD1"},{"name":"路段2","value":"LD2"}]');

查询SQL:
select d.element ->> 'value' as value,d.* from (
select json_array_elements(values::json) as element
            from t_param_config
            where key = 'roadsect'
) d where d.element ->> 'name' like '%路段1%' 

2.获取树形层级结构[-1.test0.screenType]的最后两位,该树形结构长度不定

1.使用reverse对数据进行倒序,然后用split截取第一个和第二个,再置反即可

reverse(split_part(reverse(s.tree),'.',2)) as test0,
reverse(split_part(reverse(s.tree),'.',1)) as screenType,

如何从PostgreSQL json中提取数组

3.查询字段全部转换为json格式输出

select array_to_json(array_agg(row_to_json(t))) from (select 字段1,字段2,字段3 from table_a) t

PGSQL 查询直接得到json

4.从json数组中按照下标拆除数据和json对象数据平铺

WITH pass_point AS (
	select '[{"geometry":[113.9450544,22.5095942],"imgType":"road","text":"点1","name":"深圳人才公园"},{"geometry":[113.9478610,22.5092810],"imgType":"road","text":"点2","name":"深圳湾草地公园"}]'::json as pass_point
)
select 
	pg_typeof(geom),
	j.name,
	(geom[1]::text)::numeric as lng,
	(geom[2]::text)::numeric as lat
from 
(select 
	json_array_elements(t.pass_point::json)->> 'name' as name,
	array(select json_array_elements(json_array_elements(t.pass_point::json)-> 'geometry')) as geom
from pass_point t
) j

其中pg_typeof可以查看当前对象输出的数据是什么格式,
array()函数会把查到的参数转换为json[],之后操作就和java中按照下标取值一样

参考:
postgres 数组中获取最后一个元素的值

需要先将数组的上届值或者长度查询出来,然后用长度作为下标来获取数组中的值
SELECT (ARRAY[1,2,5,6,3])[array_upper(ARRAY[1,2,5,6,3], 1)];

PG数据库json对象以及json数组官方操作文档

5.从json数组中按照下标拆除数据和json对象数组

WITH device AS (
	select '[{"key": "1", "content": "dfrd85625", "deviceIdList": ["12352frd62", "2564fgtd"]}]'::json as device_Id
)
-- select * from device
select 
pg_typeof(json_array_elements_text((json_array_elements(t.device_Id::json)->> 'deviceIdList')::json)) AS array_element,
json_array_elements_text((json_array_elements(t.device_Id::json)->> 'deviceIdList')::json) AS array_element
from device t

如果使用json_array_elements查询出来的会带双引号的json字段,需要使用json_array_elements_text返回才是text字段

6.往json扩展字段中加入新key值和value

准备工作：
6.1、表创建：

CREATE TABLE "public"."t_dev_json" (
  "id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL DEFAULT md5(((uuid_generate_v4())::character varying)::text),
  "remark" varchar(255) COLLATE "pg_catalog"."default",
  "isdelete" char(1) COLLATE "pg_catalog"."default",
  "creator" varchar(32) COLLATE "pg_catalog"."default",
  "createtime" timestamp(6) DEFAULT now(),
  "updater" varchar(32) COLLATE "pg_catalog"."default",
  "updatetime" timestamp(6) DEFAULT now(),
  "flag" varchar(32) COLLATE "pg_catalog"."default",
  "extra_param" jsonb,
  CONSTRAINT "t_dev_json_copy1_pkey6" PRIMARY KEY ("id")
)
INSERT INTO "public"."t_dev_json"("id", "remark", "isdelete", "creator", "createtime", "updater", "updatetime", "flag", "extra_param") VALUES ('1', 'json扩展字段', '0', 'admin', '2022-07-13 09:51:04.778388', NULL, '2022-07-13 09:51:04.778388', '0', NULL);

6.2、查询jsonb字段
查询数据如下，目前extra_param为null

6.3、往id=1的数据新增扩展字段{“address”:“某某路段”}
如果extra_param为null,记得一定要用COALESCE转换null值为json,否则插入新key值不会有反应

update t_dev_json t
set extra_param= COALESCE(t.extra_param, '{}') || ('{"address":"某某路段"}')::jsonb
where t.id = '1'

6.4 插入结果如下

6.5、如果需要重新更新extra_param的address字段,也可以使用同样的语句更新

update t_dev_json t
set extra_param= COALESCE(t.extra_param, '{}') || ('{"address":"某某路段更新"}')::jsonb
where t.id = '1'

6.6、删除extra_param的address字段

update t_dev_json set extra_param=extra_param - 'address' where id = '1'

参考：
Postgresql 处理jsonb字段
postgresql 实现修改jsonb字段中的某一个值

7.使用数组函数计算两个数组重叠（具有公共元素）

with t_content as (
	select 'aa,bb,cc,group_a' as user_id, 'content' as content union 
	select 'bb,group_b,dd' as user_id, 'content' as content
),
t_group as (
	select 'aa' as user_id, 'group_a' as group_id union 
	select 'bb' as user_id, 'group_b' as group_id
)
select * from t_content t
where 1=1
AND string_to_array(t.user_id, ',')
				&& (select array_prepend('aa', array_agg(group_id::VARCHAR)) from t_group where user_id = 'aa')::text[]

释意:
查询t_content中用户user_id字段既含有aa用户以及含有aa所属组的数据,使用数组函数&&可以很好解决多对多的重叠判断问题

数组函数和操作符