大家好,在开发的过程中由于业务复杂多变,普通的字符串格式满足不了业务开发需求,可能会用到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)这个函数,查询自己字段函数类型