arrays - PostgreSQL : Sorting JSON array by numeric key value

Question

我一直在阅读类似的问题，这些问题看起来有足够简单的解决方案，但在这里应用它们似乎对我不起作用。我在包含 x 和 y 坐标的 postgres 数据库表中有一个 json 列。在此列上执行简单的选择会返回以下内容，例如:

[
{
 "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"
}, 
               .
               .
               .
               .
]

我想返回这个按照 x 坐标升序排序的对象。上面的结果集实际上是运行以下查询的结果:

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

我也试过用

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

产生以下结果:

ERROR:  cannot cast type json to numeric

我确定我错过了一些愚蠢的东西。任何指向正确方向的指示都将不胜感激。

Answer 1

您应该在 json_agg() 中使用 order by。您可能想要定义一个在各种上下文中都有用的函数:

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

如果您不喜欢自定义函数，请在横向连接中使用 json_array_elements():

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

在 db<>fiddle. 中测试它