creating json array using column name as key and column as value in postgres

Question

i have table named list in a PostgreSQL database

create  table list (firstname text,lastname text,age integer);
insert into list values ('SHARON','XAVIER',25);
insert into list values ('RON','PETER',17);
insert into list values ('KIM','BENNY',14);

 select * from list;

 firstname | lastname | age 
-----------+----------+-----
 SHARON    | XAVIER   |  25
 RON       | PETER    |  17
 KIM       | BENNY    |  14

i need to create JSON array from this table like this ::

[ column name : column value]

  [
    { "firstname" : "SHARON","lastname" : "XAVIER" , "age" : 25},
    { "firstname" : "RON","lastname" : "PETER" , "age" : 17},
    { "firstname" : "KIM","lastname" : "BENNY" , "age" : 14}
  ]

any possible options ?

Answer 1

You can use to_jsonb() to convert an entire row to a JSON value, then use jsonb_agg() to aggregate all those into a single JSON array:

select jsonb_agg(to_jsonb(l))
from list l;

Online example