Sort by JSON field values

user_1337149 0
Viewed 22407

I have a table with json values like this:

-Table 1

id    |   name   |   data
------+----------+---------------
1     | Test     | {"city_id": 3, "email":"test@test.com", "city_name":"something"}
2     | Test 2   | {"city_id": 1, "email":"test2@test2.com", "city_name":"another"}
3     | Test 3   | {"city_id": 6, "email":"test3@test3.com", "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.

mysql json
4 Answers

I usually cast the JSON value (->>) to the correct type in order to sort properly:

SELECT id, name, data->>'$.city_name' AS cityName
FROM table1
ORDER BY CAST(cityName AS CHAR) ASC

Otherwise, you end up sorting as a blob (binary), which are treated as binary strings (byte strings) and thus they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values (ref).

user_10524030

the easiest way in my opinion

SELECT * FROM YourTable order by data->"$.city_name" desc
user_109040820
Related