0

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

Oytun Emre Yücel
  • 133
  • 1
  • 8

1 Answers1

0

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.

Community
  • 1
  • 1
ChelowekKot
  • 1,946
  • 1
  • 18
  • 16