按 hstore 中任意属性的值排序 [英] Order by a value of an arbitrary attribute in hstore

问题描述

我有这样的记录:

id, hstore_col
1,  {a: 1, b: 2}
2,  {c: 3, d: 4}
3,  {e: 1, f: 5}

如何在 hstore 中为 any 属性按最大值/最小值对它们进行排序?

How to order them by a maximum/minimum value inside hstore for any attribute?

结果应该是这样的(按最低排序):

The result should be like this(order by lowest):

id, hstore_col
1,  {a: 1, b: 2}
3,  {e: 1, f: 5}
2,  {c: 3, d: 4}

我知道，我只能按这样的特定属性对它们进行排序:my_table.hstore_fields ->'a'，但它对我的问题不起作用.

I know, I can only order them by specific attribute like this: my_table.hstore_fields -> 'a', but it doesn't work for my issue.

推荐答案

使用 avals 转换为数组并将结果数组从文本转换为整数.然后对数组进行排序，并按排序后的数组的第一个元素对结果进行排序.

Convert to an array using avals and cast the resulting array from text to ints. Then sort the array and order the results by the 1st element of the sorted array.

select * from mytable
order by (sort(avals(attributes)::int[]))[1]

http://sqlfiddle.com/#!15/84f31/5

这篇关于按 hstore 中任意属性的值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！