logo
podcast Podcast
get help Get Unstuck

Don't rely on key ordering with MySQL JSON values

This behavior caught me by surprise

Joel Clermont
Joel Clermont
2023-12-08

You've likely heard that it's dangerous to rely on array keys maintaining a specific order. The JSON spec even defines these key/value pairs as "unordered". But maybe in practice, you've never bumped into a situation where the order changed from what you expected.

Recently I was building a simple dashboard that collected data from an upstream API and presented it as a downloadable CSV report for the end user.

Part of the goal was to let the upstream API define the order and headings for each of the columns of data. I would store the payload from the API responses in a single JSON column in the database, in order to use the exact data definition later when assembling the report. But what I noticed was that the order of the string array keys was not preserved when loading it from the database.

// data as it came from the API
$payload = [
    'first' => 'abc',
    'middle' => 'jkl',
    'last' => 'xyz',
];

// data when loading from MySQL
$payload = [
    'last' => 'xyz',
    'first' => 'abc',
    'middle' => 'jkl',
];

I was able to work around the problem, but it made me wonder why this was happening. Was it an issue with PHP serialization, JSON decoding/encoding, Eloquent property casting, or something else?

I dug through all the different layers of Laravel, but it turns out the issue is within MySQL. For reasons of performance and consistency, MySQL normalizes JSON values when saving to the database.

This explains why my arrays were getting re-ordered just by saving and loading from the database!

And before you start relying on the particular normalization strategy MySQL uses, check out this warning from the docs: "You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases."

Overall, the general advice to never rely on a specific ordering of keys is a good one to remember. If the order really matters, make sure to track it some other way explicitly.

Here to help,

Joel

P.S. If you're ever stuck on a weird Laravel issue like this, don't suffer alone!

Toss a coin in the jar if you found this helpful.
Want a tip like this in your inbox every weekday? Sign up below 👇🏼
email
No spam. Only real-world advice you can use.