Postgres 9.3 JSON Output multi-dimensional object

Question

Given this query:-

SELECT id as id,
       attributes->>'name' as file_name, 
       status 
from workflow.events 
where schema='customer' 
  and type='FILE_UPLOAD'

id,file_name, status
1,name,status
2,name2,status2

I want to output this structure:-

{
 "1" :{"id" :"1", "file_name" : "name", "status" : "status1"},
 "2" :{"id" :"2", "file_name" : "name2","status" : "status2"}
}

I can do it at the moment using string functions but this seems messy and inefficient. CAn it be done using the native postgresql json functions?

There are a couple of useful json functions, namely row_to_json(), which should come in handy: postgresql.org/docs/current/static/functions-json.html — Denis de Bernardy
– Denis de Bernardy, Commented Oct 15, 2013 at 11:34

roman · Accepted Answer · 2013-10-15 16:50:49Z

4

If you want to get two records with json, use row_to_json() function:

with cte as (
    select 
        id as id,
        attributes->>'name' as file_name, 
        status 
     from workflow.events 
     where schema='customer' and type='FILE_UPLOAD'
)
select row_to_json(c) from cte as c

output:

{"id":1,"file_name":"name","status":"status"}
{"id":2,"file_name":"name2","status":"status2"}

If you want to get json array:

with cte as (
    select 
        id as id,
        attributes->>'name' as file_name, 
        status 
     from workflow.events 
     where schema='customer' and type='FILE_UPLOAD'
)
select json_agg(c) from cte as c

output:

[{"id":1,"file_name":"name","status":"status"}, 
 {"id":2,"file_name":"name2","status":"status2"}]

But for you desired output, I can only suggest string transformation:

with cte as (
    select 
        id::text as id,
        file_name, 
        status 
    from workflow.events 
    where schema='customer' and type='FILE_UPLOAD'
)
select ('{' || string_agg('"' || id || '":' || row_to_json(c), ',') || '}')::json from cte as c

sql fiddle demo

edited Oct 15, 2013 at 16:50

answered Oct 15, 2013 at 13:51

roman

118k30 gold badges205 silver badges209 bronze badges

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

3 Comments

Erwin Brandstetter Over a year ago

The final step with concatenation, that should be good enough after row_to_json() did most of the work. I'll delete my answer since you undeleted yours, and your answer is is good.

roman Over a year ago

Thanks @ErwinBrandstetter, I see that PostgreSQL have json_each to fetch data from json structured like this output, but seems it don't have function to do it other way around as OP wants :(

Valerio Over a year ago

There is very little of examples about those functionalities with JSON in postgres 9.3, this answer is enlightening! Thanks Roman

Collectives™ on Stack Overflow

Postgres 9.3 JSON Output multi-dimensional object

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related