I have a POSTGRESQL version 12.5 database where I have a table that has three columns: c_id
, columna
and columnb
. The three columns can have a different values.
I need to do a join their values into a single object like this:
Here is the sample data I have a table that has 3 columns with the same type
c_id columna columnb
1 a b
2 c d
3 x y
I need to run a query that will join the columns columna
and columnb
like this:
c_id merge_column
1 {"columna":a, "columnb": "b"}
2 {"columna":d, "columnb": "d"}
3 {"columna":x, "columnb": "y"}
Any ideas?
Thanks in advance
CodePudding user response:
You can convert the whole row into a JSON, the remove the c_id
key:
select t.c_id, to_jsonb(t) - 'c_id' as merge_column
from the_table t
If there are more columns than you have shown, and you only want to get two of them, using jsonb_build_object()
is probably easier:
select t.c_id,
jsonb_build_object('columna', t.columna, 'columnb', t.columnb) as merge_column
from the_table t