0

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 ?

SHARON XAVIER
  • 272
  • 5
  • 19
  • https://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array/24006432 Hope this can help – Swapnil Soni Aug 27 '21 at 10:07

1 Answers1

2

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