mysql - Sort by JSON field values

Question

I have a table with json values like this:

-Table 1

id    |   name   |   data
------+----------+---------------
1     | Test     | {"city_id": 3, "email":"[email protected]", "city_name":"something"}
2     | Test 2   | {"city_id": 1, "email":"[email protected]", "city_name":"another"}
3     | Test 3   | {"city_id": 6, "email":"[email protected]", "city_name":"blahblah"}

Now I want SELECT records with order by data.city_name, so I use this code:

SELECT id, name, JSON_EXTRACT(data, 'city_name') AS cityName
FROM table1
ORDER BY cityName ASC

but this query cannot sort my records correctly !

P.S: city_name have UTF-8 characters.

Answer 1

you do not seem to be using JSON_EXTRACT() properly, try with:

SELECT id, name, JSON_EXTRACT(data, '$.city_name') AS cityName 
FROM demo ORDER BY cityName ASC

Demo Fiddle