How to update JSONB column with value coming from another table column in PostgreSQL

Question 1

I have a source table, which lists like below:

public.source

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

Then, I have target table with jsonb column (combinations) , which list like below;

public.target

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

My problem is - how I can update the target table with jsonb column (combinations) with the values coming from source table using the part_no column?
Output like:

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

I tried below but giving error:

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

ERROR: invalid input syntax for type json LINE 2: SET combinations = jsonb_set(combinations,'{subject}', 's.categor... ^ DETAIL: Token "s" is invalid. CONTEXT: JSON data, line 1: s... SQL state: 22P02 Character: 77

Question 2

You should use to_jsonb function to convert s.category to JSON

Demo

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

Or you can use sample structure for join and update two JSON field:

Demo

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 · Answer 1 · 2021-11-16T15:26:04

You should use to_jsonb function to convert s.category to JSON

Demo

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

Or you can use sample structure for join and update two JSON field:

Demo

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

How to update JSONB column with value coming from another table column in PostgreSQL

In other languages

This page is in other languages

Popular in the category