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?
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:
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;
...
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments