Search code examples
sqlpostgresqlhstore

Order by a value of an arbitrary attribute in hstore

I have records like these:

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

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}

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.

Solution

  • 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