Postgres set jsonb field with columns from the same table

Question

I am trying to move data from a couple of columns to a single JSONB column.

For example, I have x,y,z columns like this :

id | x | y | z | data
---------------------
1  | 1 | 2 | 3 | NULL
2  | 4 | 5 | 6 | NULL
3  | 7 | 8 | 9 | NULL

And I want to make it like that :

id | x | y | z | data
---------------------
1  | 1 | 2 | 3 | {"x":1, "y":2, "z": 3}
2  | 4 | 5 | 6 | {"x":4, "y":5, "z": 6}
3  | 7 | 8 | 9 | {"x":7, "y":8, "z": 9}

I tried unsuccessfully with jsonb_set and jsonb_insert. I am using postgresql 14.

select id, x,y,z, jsonb_build_object('x', x, 'y', y, 'z', z) AS data from your_table — Edouard
– Edouard, Commented Dec 17, 2021 at 15:00

user330315 · Accepted Answer · 2021-12-17 15:05:01Z

1

You can convert the entire row to a json value, then remove the id column

select t.*,
       to_jsonb(t) - 'id' as data
from the_table t;

answered Dec 17, 2021 at 15:05

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Stefanov.sm Over a year ago

You have forgotten to subtract 'data' :)

user330315 Over a year ago

@Stefanov.sm: I assumed it's supposed to be a new column in the SELECT, not an existing one inside the table.

Dimitar Kalinov · Accepted Answer · 2021-12-17 15:07:59Z

0

OK, thanks to @Edouard I've made the query :

UPDATE table SET data = jsonb_build_object('x', x, 'y', y, 'z', z);

answered Dec 17, 2021 at 15:07

Dimitar Kalinov

433 bronze badges

Comments

Stefanov.sm · Accepted Answer · 2021-12-17 15:15:37Z

0

An update version of the solution by @a_horse_with_no_name:

update the_table set data = to_jsonb(the_table.*) - 'id' - 'data';

Unlike the jsonb_build_object approach it works w/o change for whatever number of columns except id and data.

edited Dec 17, 2021 at 15:15

answered Dec 17, 2021 at 15:10

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgres set jsonb field with columns from the same table

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related