Ordering within a json_agg of json objects

I have a simple query as follows:

select json_agg(json_build_object(
                'name','name',
                'score',random(),
                'snippet','random text'
                )) as data
from table;

I have simplified the above for the purposes of this question.

This results in something like this being returned:

[{"name" : "name", "score" : 0.896277624648064, "snippet" : "random text"},
{"name" : "name", "score" : 0.845714892726392, "snippet" : "random text"},
{"name" : "name", "score" : 0.688958259299397, "snippet" : "random text"},
{"name" : "name", "score" : 0.870620720088482, "snippet" : "random text"},
{"name" : "name", "score" : 0.282366622239351, "snippet" : "random text"}]

Is there a way I can order the underlying json_agg by the score within the object?

I’ve tried:

select json_agg(json_build_object(
                'name','name',
                'score',random(),
                'snippet','random text'
                ) order by score) as data
from table;

And also this:

select json_agg(json_build_object(
                'name','name',
                'score',random(),
                'snippet','random text'
                ) a order by a->>'score') as data
from table;

But both of the above fail

Answer

I don’t see how random() fits in. But you can generate the record in the from clause and then access the field:

select json_agg(jobj order by jobj->>'score')
from table t cross join lateral
     (values (json_build_object('name', 'name',
                                'score', random(),
                                'snippet', 'random text'
                               )
             )
     ) v(jobj);

The problem is that Postgres over-optimizes the above and returns the same score for all rows. One solution is to correlate the subquery, forcing evaluation for each row in t. Here is one method that works:

select json_agg(jobj order by jobj->>'score')
from t cross join lateral
     (values (json_build_object('name', 'name',
                                'score', (case when t.n is null then random() else random() end),
                                'snippet', 'random text'
                               )
             )
     ) v(jobj);

Here is a db<>fiddle.