MySQL. Wrong order by json fied containing utf-8 string

Question

I have a field title of type json that contains translations for different locales. It looks like

{'en'=>'Title', 'uk'=>'Заголовок'}

I'm trying to order records by a translation

select id, slug, title->>'$.uk' as locale_title from blog_posts
order by locale_title

It works for en locale with Latin symbols but for uk (Ukrainian) locale with Cyrillic symbols I get the wrong order like і, а, б, я. For other text fields (not json) ordering works as expected а, б, і, я

Additional info

Mysql version: 5.7.25

database collation: 'utf8mb4_unicode_ci'

score 1 · Accepted Answer · answered May 18 '19 at 08:15

It turns out that the collation of database and the collation of json operators are different:

collation(title->>'$.uk') //utf8mb4_bin
collation(other_field) //utf8mb4_unicode_ci

To fix my problem I should set utf8mb4_unicode_ci collation for json value explicitly:

select id, slug, title->>'$.uk' as locale_title from blog_posts
order by locale_title collate utf8mb4_unicode_ci

MySQL. Wrong order by json fied containing utf-8 string

1 Answers1