Sorting jsonb objects in Postgresql 9.4

Question

The problem that I am facing is not that I'm not able to perform a sort, but rather a correct sort. That is, my objects that are stored as jsonb need to be sorted before getting displayed in a table. Part of the query that sorts is:

ORDER BY data ->> 'Name' ASC

However the problem is that at its current state, psql returns the list of people ordered by two clusters: upper and lower case. ASC sort returns sorted upcase + sorted downcase while DESC returns inverted sort downcase + inverted sort upcase.

Is there a trick behind sorting the data in a caseless order or does the data need to initially be stored in a particular case.

ORDER BY lower(data ->> 'Name') ASC

This does create a temporary fix, but I will be glad if there are other methods out there

Answer 1

Sorting by jsonb value works the same as sorting by simple text field. If you get case-sensitive sorting, you likely set incorrect collation to your database.

See this issue, answer by Michał Niklas.