Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table my_table(coordinates json);
insert into my_table values
('[
{
 "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"
} 
]'::json)


CREATE TABLE



INSERT 0 1


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

CREATE FUNCTION




sort_my_array


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



SELECT 1


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


json_agg


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



SELECT 1