POSTGRESQL: Join columns

Question

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?

user330315 · Accepted Answer · 2022-03-10 11:14:25Z

2

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

answered Mar 10, 2022 at 11:14

user330315

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

Collectives™ on Stack Overflow

POSTGRESQL: Join columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related