Is the order of rows preserved between aggregate function calls in PostgreSQL-CodePudding

Given the example below:

create table test (
    g text,      -- group
    a integer,   -- key
    b text       -- value
)

insert into test 
select * from 
(values (1, 3, 'c'), 
        (2, 4, 'd'), 
        (2, 1, 'a'), 
        (1, 2, 'b'), 
        (2, 6, 'f'),  
        (2, 5, 'e')) as pairs (groups, num, letter)

select g, json_object(array_agg(cast (a as text)), array_agg(b)) 
from test
group by g

The result of this snippet is:

2   {"4" : "d", "1" : "a", "6" : "f", "5" : "e"}
1   {"3" : "c", "2" : "b"}

Which is perfect for my purposes, that is, the key-value pairs are preserved between the two array_agg function call. So it seems to me that these functions receive the rows in the same order. The particular order does not matter to me, only that it has to be same for both function calls, so that json_object will generate the pairs correctly. I wonder though if this is guaranteed or this is by accident and I should not rely on this. Also, if that is the case, what would be a solution to achieve the same result.

CodePudding user response：

There is no need to rely on any implicit order. You can simplify your query using the jsonb_object_agg() function:

select g, jsonb_object_agg(cast (a as text), b)
from test
group by g

Note that jsonb doesn't guarantee any order of keys inside the JSON value as the order of the keys isn't relevant.

Online example