如果你想用json获取两条记录,使用row_to_json() http://www.postgresql.org/docs/current/interactive/functions-json.html功能:
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"}
如果你想获取json数组:
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"}]
但对于您想要的输出,我只能建议字符串转换:
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 http://sqlfiddle.com/#!12/a22b1/7