Postgres set jsonb field with columns from the same table-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

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

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

CodePudding user response：

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.