如何在JSONB类型列中对数组重新排序 [英] How to reorder array in JSONB type column

问题描述

在PostgreSQL表中，一个JSONB类型列，内部存储的值是一个数组[3,6,78,1].

In PostgreSQL table, a JSONB type column, and the value stored inside is an array [3,6,78,1].

我应该怎么做才能像[1,3,6,78]那样重新排序?

What should I do to reorder it like [1,3,6,78]?

推荐答案

使用jsonb_array_elements()取消嵌套数组，并使用jsonb_agg()聚合其排序后的元素:

Unnest the array with jsonb_array_elements() and aggregate its sorted elements using jsonb_agg():

with the_data(val) as (values ('[3,6,78,1]'::jsonb))

select jsonb_agg(elem order by elem) as val
from the_data
cross join lateral jsonb_array_elements(val) as arr(elem);

      val      
---------------
 [1, 3, 6, 78]
(1 row)

您可以在自定义函数中使用该查询，该函数在更复杂的查询中非常方便:

You can use the query in a custom function which will be handy in more complex queries:

create or replace function jsonb_sort_array(jsonb)
returns jsonb language sql immutable
as $$
    select jsonb_agg(elem order by elem)
    from jsonb_array_elements($1) as arr(elem)
$$;

with the_data(val) as (values ('[3,6,78,1]'::jsonb))

select jsonb_sort_array(val) as val
from the_data;

      val      
---------------
 [1, 3, 6, 78]
(1 row)

这篇关于如何在JSONB类型列中对数组重新排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！