How to sort objects in an array inside a json or jsonb value by a property of the objects?

Question

I have this pl/pgsql function to aggregate rows from two tables in a jsonb value (data_table_1 and data_table_2). fk_id is a common foreign key id in both tables:

DECLARE
v_my_variable_1 jsonb;
v_my_variable_2 jsonb;
v_combined      jsonb;
BEGIN
  SELECT json_agg( data_table_1 ) INTO v_my_variable FROM data_table_1 WHERE fk_id = v_id;
  SELECT json_agg( data_table_2 ) into v_my_variable_2 FROM data_table_2 WHERE fk_id = v_id;
  SELECT v_my_variable || v_my_variable_2 into v_combined;

Now I want to sort v_combined by the field ts, a timestamp column common to both tables, and consequently a common key in all array objects in the jsonb value.

Example:

v_combined = '[{"id": 1, "type": 4, "param": 3, "ts": 12354355}
             , {"id": 1, "txt": "something", "args": 5, "ts": 12354345}]';

How do I sort array elements in v_combined in ascending order for ts?

If I were selecting from a table I could simply use:

select * into v_combined from v_combined ORDER BY v_combined->>'ts' ASC;

But when I try that, it says that v_combined does not exist. Is there a way of storing it in a temp table and sorting there, or is there a direct way to sort the array of json objects in pl/pgsql?

Answer 1

The order of keys in an object in a jsonb literal is insignificant - object keys are sorted internally anyway. (json is different in this regard.) See:

• Customize jsonb key sort order involving arrays

The order of array elements in a jsonb (or json) literal is significant, though. Your request is meaningful. You can reorder like this:

SELECT jsonb_agg(elem)
FROM  (
   SELECT *
   FROM   jsonb_array_elements(v_combined) a(elem)
   ORDER  BY (elem->>'ts')::int  -- order by integer value of "ts"
   ) sub;

dbfiddle here

But it would be more efficient to order the array before assigning it:

...
DECLARE
   v_combined      jsonb;
BEGIN
   SELECT INTO v_combined  jsonb_agg(elem)
   FROM  (
      SELECT ts, json_agg(data_table_1) AS j
      FROM   data_table_1
      WHERE  fk_id = v_id

      UNION ALL 
      SELECT ts, json_agg(data_table_2)
      FROM   data_table_2
      WHERE  fk_id = v_id
      ORDER  BY ts
      ) sub;
...

On the order of rows from a subquery

In standard SQL the order of rows in a subquery (or any table expression) is insignificant as well. But in Postgres the order of rows in subqueries is carried over to the next level. So this works in simple queries. It's even documented:

... supplying the input values from a sorted subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

Beware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed.

If you can't or won't rely on this, there is a safe alternative: add an ORDER BY to the aggregate function itself. That's even shorter:

SELECT INTO v_combined  jsonb_agg(elem  ORDER BY (elem->>'ts')::int)
FROM   jsonb_array_elements(v_combined) a(elem);

But it's typically slower.