knex Sort by JSON field values

user_13653568 0
Viewed 141

I am using knex with mysql and 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"}

how can i sort this table with data.city_name desc and asc?

knex.js node.js mysql
1 Answers

you can do like this

knex.table('YourTableName').orderByRaw(`data->"$.city_name" desc`);
user_109040820
Related