PostgreSQL 关于Jsonb字段的处理详解（长期更）

大家好，在开发的过程中由于业务复杂多变，普通的字符串格式满足不了业务开发需求，可能会用到jsonb字段去处理格外逻辑的业务。

话不多说，

1.一般jsonb存储的逻辑是，一个字段里面存储多条数据，以id为存储对象举例，该jsonb字段中存储的多个对象的id，如图所展示

2.其次就是jsonb字段里面存储的是以k，v键值对形式的数据。

根据这两种情况我们如何对其数据信息筛选或者处理呢，首先是根据条件2的情况去进行提取需要的数据

SELECT base_message_info ->>'dialog' FROM "base_message"

//解释：查询 该字段中k为 'dialog'对应v 的值 在后面的表中

结果：

其次根据条件1的情况去提取需要的数据，

敲重点，假设一个业务，我们需要将jsonb里面存储的id进行连表查询，且里面存储的数据是需要去掉括号以及字符串的，并且需要列转行，那需要怎么办呢？

笔者这里提供一种解决方式

1.将数据全部先转成字符串的数据类型，因为连表查询的数据类型要一致

( base_authority_condition_button.sys_role_ids ) :: CHARACTER VARYING AS sys_role_ids 

2.其次就是将jsonb里面的[],"",这些字符去掉，或转化为空字符串，然后列转行

SELECT
	v_attribute_permission.ID,
	v_attribute_permission.NAME,
	regexp_split_to_table(
		REPLACE (
			REPLACE ( REPLACE ( ( v_attribute_permission.sys_role_ids ) :: TEXT, '[' :: TEXT, '' :: TEXT ), '"' :: TEXT, '' :: TEXT ),
			']' :: TEXT,
			'' :: TEXT 
		),
		'\,' :: TEXT 
	) AS sys_role_ids 
FROM
	v_attribute_permission 
WHERE
	( ( v_attribute_permission.del_flag ) :: TEXT = '1' :: TEXT )

//解释一下列传行笔者用的函数是 regexp_split_to_table,就是将jonsb里面的数据根据特定的字符串进行转行
//代替用的是replace，就是将数据里面的所有指定的数据中的字符串转化为期待的字符

3，将字符串转化为jsonb格式，且行转列用 jsonb_object_agg 这个函数

4，如果不确定转换之后的格式是什么可以用到 pg_typeof(your_variable)这个函数，查询自己字段函数类型