开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第15天，点击查看活动详情

业务中有这样的需求，有促销活动表 promotion_table 、会员等级表 level_table，它们的联系是：添加促销活动的时候可以选择哪种等级的可以参与，等级信息可以选择参加的促销活动，属于双向关联。 但是系统现在只实现了一半（假设promotion_table 没有content的场景），也就是 等级信息可以选择参加的促销活动但是另一种方式还没有体现，如下：

表 promotion_table

id (varchar)	name	content (json)
1	促销活动1	{"level_id":"1111111111,2222222"}
2	促销活动2	{"level_id":"1111111111"}

表 level_table

id (varchar)	name	content (json)
1111111111	等级1	{"promotion_id":["1","2"]}
2222222	等级2	{"promotion_id":["1"]}

假设现在给promotion_table 加了 content 字段 你如何在上线的时候把关联数据给刷上去？

1. 首先建立关联字段联系

两个表进行内连接，关联字段就是 promotion_table 的id 和 level_table 的 content 的 promotion_id，因为是json里面的数据，你该这样关联：column_a :: jsonb ? column_b 表示的是 column_a 字段的数据是否包含 column_b

select * from promotion_talbe pt, level_table lt
where lt.content ::jsonb ? pt.id

2. group 分组后 进行join

select 
a.promotion_id,
string_agg(a.level_id, ',') as level_str

from (
select pt.id as promotion_id,lt.id as level_id from promotion_talbe pt, level_table lt
where lt.content ::jsonb ? pt.id

) a
group by a.promotion_id

3. 整理 level_str 为json格式

需要用的字符串拼接

select 
a.promotion_id,"{"level_id":"||string_agg(a.level_id, ',') as level_str || '}'
from (
select pt.id as promotion_id,lt.id as level_id from promotion_talbe pt, level_table lt
where lt.content ::jsonb ? pt.id

) a
group by a.promotion_id

4. 然后需要用 update join 进行关联修改

中间需要用到给json字段追加字符串

UPDATE promotion_table AS pt 
SET pt.json = pt.json || lt.level_str
FROM 
(select a.promotion_id,"{"level_id":"||string_agg(a.level_id, ',') as level_str || '}' from ( select pt.id as promotion_id,lt.id as level_id from promotion_talbe pt, level_table lt where lt.content ::jsonb ? pt.id ) a group by a.promotion_id) 
AS lt
WHERE pt.id = lt.promotion_id 

一个小小的需求用到了几乎很多不常用的方法和语法~ 下面整理了整个业务用的所有的功能

字符串操作

拼接

select 'aaa' || 'bbb'

加双引号

select quote_ident('123'); // 可以
select quote_ident('aa'); // 不会加双引号

查询

group join

string_agg 函数

SELECT id,string_agg(name, ',')name FROM table GROUP BY id

id	name
1	张三,李四,王五
2	张三1,李四2,王五2

update join

UPDATE table_1 AS table1 
SET column1 = table2.column2
FROM table2 AS table2
WHERE table1.column3 = table2.column3 

json

取json中某个key的value

-- 这里是造了个json字符串，正常情况直接去对应字段，且该字段必须是json类型的，不能是varchar类型
select '{"key":"value"}'::jsonb ->>'key' 

判断是否含有指定 key

select '{"key":"value"}'::jsonb ?'key1' // false

判断数组中是否含有某个元素

select '["aa","bb","cc"]' ::jsonb ? 'aa'   // true
select '["aa","bb","cc"]' ::jsonb ? 'dd'   // false

给json追加指定key

-- 多次执行就是替换逻辑
-- {"a":"b","c":"c"} ->> {"a":"b","c":"c","key":"value"}
update table set josn_content = json_content || '{"key":"value"}'

类型转换

varchar -> json

如果这个字符串是json格式的，可以使用 ::jsonb 来转换成json类型

select '{"key":"value"}' :: jsonb

long -> varchar

select 1 :: varchar