Json_agg as single object-CodePudding

I have following table:

create table tickets (id integer, key text, value text);

insert into tickets values
(1, 'CUSTOMER_ID', 3242),
(1, 'AMOUNT', 49),
(1, 'MOVIE', 'TITANIC');

expected output:

{"CUSTOMER_ID": "3242", "AMOUNT": "49", "MOVIE": "TITANIC"}

If the data in the table would be stored in separated columns the simple json_build_object('name', column_name) would work, but here I have various number of rows (and they may be different for each ID)

So this one doesn't work as I want

select json_agg(jsonb_build_object(key, value)) from tickets where id = 1;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a2140d9d9ecf7adcec13cbb1d86215c8

CodePudding user response：

select jsonb_object_agg(key, value) from tickets where id = 1;

CodePudding user response：

I think you are looking for

SELECT id,
       jsonb_object_agg(key, value)
FROM tickets
GROUP BY id;