knex Sort by JSON field values-CodePudding

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":"[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"}

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

CodePudding user response：

you can do like this

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