CODEWITHSUNDEEP
mysqllaraveleloquent
Howard
Howard

Reputation: 3758

How can I do an orderBy query for data that is stored in JSON format?

So for example when the data is in string format I can do something like this:

->orderBy(DB::raw('FIELD(animal_type, "fish", "amphibian", "reptile", "bird", "mammal", "") ASC, animal_type'))

But if the data for 'animal_type' is stored in JSON format like this:

["vertebrate", "amphibian"]

Let's say I have a table that looks like this:

id animal animal_type
1 Leaf green tree frog ["vertebrate", "amphibian", "ectothermic", "pelodryadidae"]
2 Seymouria ["vertebrate", "amphibian"]
3 Dermophis mexicanus ["amphibian"]
4 Old World sparrow ["vertebrate", "bird"]
5 Parrot ["vertebrate", "bird", "psittacines"]
6 African bush elephant ["vertebrate", "mammal"]

Ideally I'd like to sort by a single characteristic of the animal. Say order by "bird", "amphibian". Then the result would look like:

Old World sparrow
Parrot
Leaf green tree frog
Seymouria
Dermophis mexicanus

How would I go about creating a query that can do an orderBy in this kind of scenario?

Upvotes: 0

Views: 63

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

MySQL 8.0 introduces a JSON function that can help: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of

Here's how it works:

mysql> select * from mytable order by 
  'bird' member of(animal_type) desc, 
  'amphibian' member of(animal_type) desc;
+----+-----------------------+--------------------------------------------+
| id | animal                | animal_type                                |
+----+-----------------------+--------------------------------------------+
|  4 | Old World sparrow     | ["vertebrate", "bird"]                     |
|  5 | Parrot                | ["vertebrate", "bird", "psittacines"]      |
|  1 | Leaf green tree frog  | ["vertebrate", "amphibian", "ectothermic"] |
|  2 | Seymouria             | ["vertebrate", "amphibian"]                |
|  3 | Dermophis mexicanus   | ["amphibian"]                              |
|  6 | African bush elephant | ["vertebrate", "mammal"]                   |
+----+-----------------------+--------------------------------------------+

Although MySQL 8.0 also supports creating a multi-valued index on JSON data to help searches for values, this only helps optimize row filtering (WHERE clause), it does not yet optimizing sorting (ORDER BY clause).

If you use a version of MySQL 5.x that doesn't support MEMBER OF(), you're out of luck. You should be making plans to upgrade anyway, because 5.x is going to be end of life in October 2023.

Upvotes: 1

Related Questions