How to sort objects in a JSON array by an object property?

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?

I don't understand what you are trying to achieve. Your code is not self-explanatory. — Laurenz Albe
– Laurenz Albe, Commented Mar 10, 2018 at 12:57
Now edited to clarify the end result that I want. Thank you for the feedback. — Halcyon
– Halcyon, Commented Mar 12, 2018 at 11:19
You have SELECT v_combined || v_my_variable into v_my_variable_2;. I assume you meant to write SELECT v_my_variable || v_my_variable_2 into v_combined;? — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 12, 2018 at 16:59
@LaurenzAlbe: I am a fan of your high-quality posts, but I suspect a misunderstanding this time: jsonb does have a concept of ordering for array elements. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 12, 2018 at 17:34
I took the liberty to fix the concatenation statement to avoid confusion. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 12, 2018 at 18:07

Erwin Brandstetter · Accepted Answer · 2020-12-27 04:02:26Z

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.

Wow, that's amazing. Thank you very much! I'd double up vote it if I could.

Collectives™ on Stack Overflow

How to sort objects in a JSON array by an object property?

1 Answer 1

On the order of rows from a subquery

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

On the order of rows from a subquery

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related