CODEWITHSUNDEEP
jsonpostgresqljsonb
mres
mres

Reputation: 63

How to make pgsql return the json array

everyone , I face some issue to convert the data into json object. There is a table called milestone with the following data:

id  name    parentId
a   test1   A
b   test2   B
c   test3   C

I want to convert the result into a json type in Postgres:

[{"id": "a", "name": "test1", "parentId": "A"}]
[{"id": "b", "name": "test2", "parentId": "B"}]
[{"id": "c", "name": "test3", "parentId": "C"}]

if there are anyone know how to handle , please let me know , thanks all

Upvotes: 0

Views: 484

Answers (2)

klin
klin

Reputation: 121889

You can get each row of the table as simple json object with to_jsonb():

select to_jsonb(m)
from milestone m

                   to_jsonb                    
-----------------------------------------------
 {"id": "a", "name": "test1", "parentid": "A"}
 {"id": "b", "name": "test2", "parentid": "B"}
 {"id": "c", "name": "test3", "parentid": "C"}
(3 rows)

If you want to get a single element array for each row, use jsonb_build_array():

select jsonb_build_array(to_jsonb(m))
from milestone m

                jsonb_build_array                
-------------------------------------------------
 [{"id": "a", "name": "test1", "parentid": "A"}]
 [{"id": "b", "name": "test2", "parentid": "B"}]
 [{"id": "c", "name": "test3", "parentid": "C"}]
(3 rows)

You can also get all rows as a json array with jsonb_agg():

select jsonb_agg(to_jsonb(m))
from milestone m

                                                                   jsonb_agg                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 [{"id": "a", "name": "test1", "parentid": "A"}, {"id": "b", "name": "test2", "parentid": "B"}, {"id": "c", "name": "test3", "parentid": "C"}]
(1 row)

Read about JSON Functions and Operators in the documentation.

Upvotes: 1

dwir182
dwir182

Reputation: 1549

You can use ROW_TO_JSON

From Documentation :

Returns the row as a JSON object. Line feeds will be added between level-1 elements if pretty_bool is true.

For the query :

select 
  row_to_json(tbl) 
from 
 (select * from tbl) as tbl;

You can check here in DEMO

Upvotes: 0

Related Questions