Search code examples
sqljsonpostgresqlknex.js

POSTGRESQL: Join columns

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?

Solution

  • 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