Postgres 9.3 JSON输出多维对象 [英] Postgres 9.3 JSON Output multi-dimensional object

问题描述

给出此查询：-

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

我要输出此结构：-

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

我现在可以使用字符串函数来做到这一点，但这似乎很杂乱且效率低下。可以使用本机postgresql json函数完成吗？

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?

推荐答案

如果要使用json获取两条记录，请使用 row_to_json（）函数：

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

输出：

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

如果要获取json数组：

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

输出：

[{"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小提琴演示

这篇关于Postgres 9.3 JSON输出多维对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！