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.

Thanks for the prompt reply @eurotrash. Unfortunately, although it doesn't throw an error, the ordering of the returning json is the exact same as what I posted initially. — Andrew Fox
– Andrew Fox, Commented Apr 9, 2018 at 16:07
Is that JSON array from one field or is each x/y from a separate record? — 404
– 404, Commented Apr 9, 2018 at 16:09

klin · Accepted Answer · 2022-05-03 22:47:25Z

13

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.

edited May 3, 2022 at 22:47

answered Apr 9, 2018 at 18:08

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Fox Over a year ago

Thanks for the suggestion @klin. This works and probably is the more elegant solution alright.

404 · Accepted Answer · 2018-04-09 16:22:26Z

1

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...

answered Apr 9, 2018 at 16:22

404

8,7522 gold badges33 silver badges53 bronze badges

1 Comment

Andrew Fox Over a year ago

Very good. I just constructed a proc which includes your suggestion and calling it works a charm. Accepting your answer. Thanks again for the assistance.

Collectives™ on Stack Overflow

Postgresql : Sorting JSON array by numeric key value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related