CODEWITHSUNDEEP
sqlpostgresql
Abhijeet Gulve
Abhijeet Gulve

Reputation: 869

How to convert recordset to json array in postgres

i'm having one table like 

  name | age 
   abc | 20
   pqr | 30

I want result in json array like 

{
 [{
   "name":"abc",
   "age":20 
 },
 {
  "name":"pqr",
   "age":30 
 }]
}

I know their is method 

row_to_json();

that will be give me only single row of json but i want array, please help me on this

Upvotes: 6

Views: 10543

Answers (2)

nmtri
nmtri

Reputation: 474

You can try: 

SELECT array_to_json(array_agg(row_to_json(data)))
FROM (select name, age from your_table) data

You also can see more at this link:

https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql

Hope it useful to you.

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

select json_agg(row_to_json(t))
from t
;
                      json_agg                      
----------------------------------------------------
 [{"name":"abc","age":20}, {"name":"pqr","age":30}]

Upvotes: 14

Related Questions