Convert rows into json object in postgresql

Question

With PostgreSQL 13 I'm trying to convert rows with 2 columns into a JSON object where a column value is the key and the other column is the value.
My table:

key      | value  
-------- | -------- 
key1     | value1           
key2     | value2        
key3     | value3

My expected result:
{"key1":"value1","key2":"value2","key3":"value3"}

I found the json_object function on postgresql website : PostgreSQL JSON functions but I can't get it to work.

I tried with other functions:

SELECT jsonb_agg(jsonb_build_object(key ,value))
FROM mytable

but it gives me an array of json objects
[{"key1":"value1"},{"key2":"value2"},{"key3":"value3"}]

I know I can still build it with string functions but I feel like it's an easy task even if I can't get it :-/

Any help would be appreciated !

user330315 · Accepted Answer · 2022-07-07 07:17:20Z

5

You can use jsonb_object_agg()

select jsonb_object_agg(key, value)
from the_table

answered Jul 7, 2022 at 7:17

user330315

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

Collectives™ on Stack Overflow

Convert rows into json object in postgresql

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