Postgresql jsonb_agg subquery sort

Question

How can I sort the results of a subquery that's using a json aggregate?

If I had a schema like this:

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);

I'm aggregating the plan_items result on a json column through a subquery. Like this:

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;

The JSON aggregate is working as expected and give me the results that I need. Ok. But I can't order the results.

I've tried:

  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

and also:

  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

But none of these solved.

Any ideas?

Answer 1

As Abelisto suggests, just use a simple aggregate expression with ordering:

jsonb_agg(plan_items ORDER BY plan_items.expected_at) AS plan_items_data

Answer 2

Join the tables with the desirable sort order and use lateral join to select columns for jsonb_agg():

select s.plan_id id, name, jsonb_agg(pi_col)
from (
    select p.id plan_id, p.name, pi.id, expected_at, status
    from plans p
    join plan_items pi 
    on p.id = pi.plan_id
    order by p.id, expected_at
    ) s,
lateral (
    select plan_id id, expected_at, status
    ) pi_col
group by 1, 2
order by 1;

The above query seems to be more natural and flexible (and a bit faster in most cases) than the one with a subquery in a select list. However for better performance you should also apply Abelisto's suggestion:

select s.plan_id id, name, json_agg(pi_col order by pi_col.expected_at)
from (
    select p.id plan_id, p.name, pi.id, expected_at, status
    from plans p
    join plan_items pi 
    on p.id = pi.plan_id
    ) s,
lateral (
    select plan_id id, expected_at, status
    ) pi_col
group by 1, 2
order by 1;