Don't rely on key ordering with MySQL JSON values

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!