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

Community · Accepted Answer · 2017-05-23 12:23:31Z

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.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Jan 12, 2016 at 5:17

ChelowekKot

2,1141 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting jsonb objects in Postgresql 9.4

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related