How to convert recordset to json array in postgres

Question

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

Clodoaldo Neto · Accepted Answer · 2017-04-20 14:32:26Z

14

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

answered Apr 20, 2017 at 14:32

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Davidson Lima Over a year ago

A detail, even if it's obvious (it wasn't for me at the first sight): t is the table name.

nmtri · Accepted Answer · 2017-04-20 14:40:25Z

4

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.

answered Apr 20, 2017 at 14:40

nmtri

4803 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to convert recordset to json array in postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related