October 30, 2020

How to Sort MySQL JSON Data Correctly

If you are doing any type of ordering with MySQL JSON especially with numbers, you might be surprised to see that it doesn't exactly work as expected.

Say we have a JSON structure that looks like this inside the records table:

{
    "price": {
      "discounted": 20.9
    }
}

A query below may return results that look like this:

# Query
SELECT price->"$.discounted" FROM records WHERE user_id = 1
ORDER BY price->"$.discounted" DESC;

# Results
11.31
13.1
14.39
16.72
28.9
29.16
5.31
5.97

Huh? Why is 5.31 lower than 29.16?

So we quickly do a double check to make sure that the numbers are not saved as strings instead. And they weren't.

What went wrong?


Gotcha with MySQL JSON Sorting

Traditionally with other non-JSON columns, MySQL knows exactly what data types they are dealing with. Hence when we order by strings it would be done in alphabetical order, if we order by integer / float it would be done by the numbers' values etc.

But with JSON data, MySQL does not have this knowledge.

As a result, MySQL "end up sorting as a blob (binary), which are treated as binary strings (byte strings) and thus they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values". (source)

In short, it's not sorting by numbers as we expected it to be.

What can we do?


Solution: Enter Type Casting

The solution is very simple. In order for MySQL to sort the data per our expectations, we need to tell MySQL what data it's sorting. We can do so by doing a cast.

Let us modify our earlier query to the one below:

# Query
SELECT price->"$.discounted" FROM records WHERE user_id = 1
ORDER BY CAST(price->"$.discounted" AS FLOAT) DESC;

# Results
5.31
5.97
11.31
13.1
14.39
16.72
28.9
29.16

Finally, they are in the right order!

***

Hello! My name is Jian Jye and I work on Laravel projects as my main job. If my article was helpful to you, a shoutout on Twitter would be awesome! I'm also available for hire if you need any help with Laravel. Contact me.