按JSON字段值排序 [英] Sort by JSON field values
本文介绍了按JSON字段值排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个表，其中包含这样的json值:
I have a table with json values like this:
-表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"}
现在我想用order by data.city_name记录SELECT，所以我使用以下代码:
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 !
PS:city_name具有UTF-8字符.
P.S: city_name have UTF-8 characters.
推荐答案
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
这篇关于按JSON字段值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
