Menu
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.
416
JSON return type is an array of objects. Hence sort method cannot be used directly to sort the array. However, we can use a comparer function as the argument of the 'sort' method to get the sorting implemented.
Enter your JSON into the first text area, or drag and drop a file, after, select the sort method you're going to use, key value requires the key name (if not specified selects the first key), click the example button to get an idea on how it works. The result will automatically sort and display in the output text area.
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
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
