postgresql - 从函数返回 SELECT 结果作为 JSON (Postgres)

Question

我有一个 plpgsql 函数和一个复杂的嵌套 SELECT 查询，我想将查询的结果表作为 JSON 返回。我该怎么做呢？我有这样的东西:

CREATE TEMPORARY TABLE selectRESULTS AS
 /*some long, complicated, and nested SELECT query
 .............
 ..............
 */
RETURN array_to_json(array_agg(selectResults));

---

编辑:我在尝试其中一种解决方案后添加了其他规范

使用 Pozs 的方法:

RETURN (SELECT json_agg(selectResults) FROM selectResults);

我确实得到了一个 json。然而，它嵌套了额外的数组，这使得内部对象更难访问。

目前我得到

[['[{"someKey": someValue}, etc]']]

有什么方法可以摆脱两个外部数组，而不是使用

somejson[0][0][0]["someKey"] 访问 someValue，我可以只使用 someJson[0]["someKey "]?

提前致谢!

Answer 1

简而言之，您的语法有误；您需要 SELECT 才能使用聚合函数，例如 array_agg。您也可以使用 array() 构造函数:

-- do NOT use these, these are far from optimal

RETURN array_to_json(array(SELECT selectResults FROM selectResults));
-- or
RETURN (SELECT array_to_json(array_agg(selectResults)) FROM selectResults);

但是json_agg(...)与 array_to_json(array_agg(...)) 效果相同但速度更快:

RETURN (SELECT json_agg(selectResults) FROM selectResults);

注意:我使用selectResults来选择selectResults表的整行，但如果你只有1列，你可以直接使用那个。

但是，您可能根本不需要临时表。如果您的逻辑就这么简单，您可以使用一个简单的 sql 函数，例如:

create function xyz(p1_type, ..., pn_type)
  returns json
  language sql
as $func$
  select json_agg(select_results)
  from (
    /*some SELECT query here
     .............
     use $1 ... $<n> for parameters here,
     or you can name them also within sql functions
     .............
     */
  ) select_results
$func$;