Postgres 9.3 JSON Output multi-dimensional object

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?

Answer1:

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

<strong>sql fiddle demo</kbd></strong>

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Postgres 9.3 JSON Output multi-dimensional object