PostgREST自定义函数平面数组，而不是key:value

Question

我有一个从数据列表查询组的自定义函数。因此，我收到了唯一的查询结果，但作为嵌套对象[{substance: 'value'}]。它希望我的回复是[value, value, value]。

SQL:

create or replace function api.substances(substance_group text) returns table(substance text) 
as 'select api.emissions.substance from api.emissions where api.emissions.substance_group = $1 group by api.emissions.substance;'
language sql;

响应：

[
{
"substance": "Arseen"
},
{
"substance": "Benzo[b]fluorantheen"
},
...
]

需要响应：

[
"Arseen",
"Benzo fluorantheen",
...
]

Answer 1

您需要返回一个文本数组(text[](而不是table(text)，并在查询中使用array_agg函数。类似：

create or replace function api.substances(substance_group text) returns text[] as $$
select array_agg(api.emissions.substance)
from api.emissions
where api.emissions.substance_group = $1
group by api.emissions.substance;
$$ language sql;