json - Postgresql jsonb_agg 子查询排序

Question

如何对使用 json 聚合的子查询的结果进行排序？

如果我有这样的模式:

CREATE TABLE plans( id integer NOT NULL, name character varying(255));
CREATE TABLE plan_items ( id integer NOT NULL, plan_id integer NOT NULL, expected_at date, status integer);

我正在通过子查询将 plan_items 结果聚合到 json 列上。像这样:

SELECT
  plans.id,
  plans.name,
  jsonb_agg((SELECT pi_cols FROM 
       (SELECT plan_items.id, plan_items.expected_at, plan_items.status) pi_cols
      )) AS plan_items_data
FROM
  plans
  INNER JOIN plan_items ON plan_items.plan_id = plans.id
GROUP BY
  plans.id,
  plans.name
ORDER BY plans.id;

JSON 聚合按预期工作，并为我提供了我需要的结果。好的。但我无法订购结果。

我试过:

  jsonb_agg((SELECT pi_cols FROM 
       (SELECT plan_items.id, plan_items.expected_at, plan_items.status ORDER BY plan_items.expected_at) pi_cols
      )) AS plan_items_data

还有:

  jsonb_agg((SELECT pi_cols FROM 
       (SELECT plan_items.id, plan_items.expected_at, plan_items.status) pi_cols ORDER BY pi_cols.expected_at
      )) AS plan_items_data

但这些都没有解决。

有什么想法吗？

Answer 1

作为Abelisto建议，只需使用一个简单的 aggregate expression with ordering :

jsonb_agg(plan_items ORDER BY plan_items.expected_at) AS plan_items_data