postgresql - 如何使用 array_agg(order by) 进行投影？

Question

我有一个包含三列的表格:id、name 和 position。我想创建一个 JSON 数组，如下所示:

[
    {"id": 443, "name": "first"},
    {"id": 645, "name": "second"}
]

这应该由 position 列列出。

我从以下查询开始:

with output as
(
    select id, name
    from   the_table
)
select array_to_json(array_agg(output))
from   output

这很管用，太棒了。现在我想添加顺序。我从这个开始:

with output as
(
    select id, name, position
    from   the_table
)
select array_to_json(array_agg(output order by output.position))
from   output

现在输出如下:

[
    {"id": 443, "name": "first", "position": 1},
    {"id": 645, "name": "second", "position": 2}
]

但我不想在输出中出现 position 字段。

我面临先有鸡还是先有蛋的问题:我需要 position 列才能对其进行排序，但我也不想要 position 列，因为我不希望它出现在结果输出中。

我该如何解决这个问题？

我认为以下查询不正确，因为(理论上)在查询之间不保留表顺序:

with output as
(
    select   id, name
    from     the_table
    order by position
)
select array_to_json(array_agg(output))
from   output

Answer 1

有两种方式(至少):

构建 JSON 对象:

with t(x,y) as (values(1,1),(2,2))
select json_agg(json_build_object('x',t.x) order by t.y) from t;

或者删除不需要的键:

with t(x,y) as (values(1,1),(2,2))
select json_agg((to_jsonb(t)-'y')::json order by t.y) from t;

请注意，在第二种情况下，您需要一些类型转换，因为 - 运算符仅为 JSONB 类型定义。

另请注意，我使用了直接 JSON 聚合 json_agg() 而不是对 array_to_json(array_agg())