如何在PostgreSQL中使用来自另一个表列的值更新JSONB列？

Question

我有一张源表格，内容如下：

public.source

Id | part_no | category
1 | 01270-4 | Landscape
2 | 01102-3 | Sports

接下来，我有一个包含jsonb列（组合）的目标表，格式如下：

public.target

Id | part_no | combinations
7 | 01270-4 | {"subject":""}
8 | 01102-3 | {"subject":""}

我的问题是如何使用part_no列中的值，将来自源表的combinations列的值更新到目标表中的jsonb列中？
输出格式如下：

Id | part_no | combinations
7 | 01270-4 | {"subject":"Landscape"}
8 | 01102-3 | {"subject":"Sports"}

我尝试了以下内容，但出现了错误：

UPDATE public.target t
SET combinations = jsonb_set(combinations,'{subject}','s.category',false)
FROM public.source s
WHERE s.part_no = t.part_no;

错误：类型为 json 的输入语法无效 第二行：SET combinations = jsonb_set(combinations,'{subject}', 's.categor... ^ 细节：标记“s”无效。 上下文：JSON 数据，第1行：s... SQL 状态：22P02 字符：77

- Bogart

Answer 1

您应该使用to_jsonb函数将s.category转换为JSON格式。

示例

UPDATE public.target t
SET combinations = jsonb_set(combinations,'{subject}',to_jsonb(s.category),false)
FROM public.source s
WHERE s.part_no = t.part_no

您可以使用示例结构来联接和更新两个JSON字段：

示例

UPDATE public.target t
SET combinations = combinations || jsonb_build_object('subject', s.category)
FROM public.source s
WHERE s.part_no = t.part_no

- Pooya

---

太好了！非常感谢你，Pooya。你真是个牛人！ - Bogart

需要注意的是，如果 s.category 的值为 null，则将忽略路径并将该值设置为 null，从而删除整个 JSON 对象。可以通过使用 jsonb_set_lax 来避免这种情况发生。 - Glenn

以下是与我上面评论相关的一些有用信息：https://www.2ndquadrant.com/en/blog/jsonb_set_lax/ - Glenn