Postgresql : Sorting JSON array by numeric key value

Question

I've been reading similar questions which look to have simple enough solutions, but applying them here doesn't seem to be working for me. I have a json column in a postgres database table which contains x and y co-ordinates. Performing a simple select on this column gives back the following for example :

[
{
 "x": "-1.6827080672804147",
 "y": "-0.011726425465745486"
},
{
 "x": "2.4016256261667235",
 "y": "0.016304356672382222"
},
{
 "x": "0.2278035109735127",
 "y": "0.0013854154958112177"
},
{
 "x": "1.2104642489702613",
 "y": "0.008129416140682903"
},
{
 "x": "-0.3281865438803838",
 "y": "-0.0024303442506510738"
},
{
 "x": "-0.2401461868455415",
 "y": "-0.0018261232803209514"
}, 
               .
               .
               .
               .
]

I would like to return this object ordered in terms of the x coordinate ascending. The above resultset was actually the result of running the following query:

  SELECT coordinates 
    FROM schema_name.table_name
   WHERE ....
     AND ....
ORDER BY coordinates ->> 'x' asc;

I also tried using

ORDER BY cast(coordinates->>'x' as numeric)  ASC

which yields the following:

ERROR:  cannot cast type json to numeric

I'm sure it's something silly I'm missing. Any pointers in the right direction would be greatly appreciated.

Answer 1

You should use order by in json_agg(). You may want to define a function that can be useful in various contexts:

create or replace function sort_my_array(json)
returns json language sql immutable as $$
    select json_agg(value order by (value->>'x')::numeric)
    from json_array_elements($1)
$$;

select sort_my_array(coordinates)
from my_table

If you do not like custom functions, use json_array_elements() in a lateral join:

select json_agg(value order by (value->>'x')::numeric)
from my_table
cross join json_array_elements(coordinates)

Test it in db<>fiddle.

Answer 2

Since all those elements are in one field, by ordering it you're transforming the data, so you'll need to take it apart, order it, then put it back together.

SELECT ('[' || STRING_AGG(j::TEXT, ',' ORDER BY (j->>'x')::NUMERIC) || ']')::JSON
FROM json_array_elements(
'[
{"x": "2.4016256261667235","y": "0.016304356672382222"},
{"x": "-1.6827080672804147","y": "-0.011726425465745486"},
{"x": "0.2278035109735127","y": "0.0013854154958112177"}
]'::JSON) j

Result:

[{"x": "-1.6827080672804147","y": "-0.011726425465745486"},{"x": "0.2278035109735127","y": "0.0013854154958112177"},{"x": "2.4016256261667235","y": "0.016304356672382222"}]

I should note that I think in later versions of PG there may be better ways to construct a JSON object than STRING_AGG'ing, but I use PG 9.3, so...