Why is MySQL with JSON Support “Fucking Tight”? – DevElevation

January 10th, 2020

Earlier this month, I wrote about MySQL with JSON support being "fucking tight".

This might be the first time I can recall quoting myself, but fair warning... I kind of like it.

Allow me to elaborate.

When you process form data through HTTP's POST protocol for example, to get it into a MySQL database previously, you would have to create a field for each, or serialize the array and store that if you want to cram it into 1 field.

The problem with serialising data is that it sucks. The way it formats the data is that it specifies how many characters are available which makes it rigid. For example, if you store strings such as email and you want to change the field directly through PHPMyAdmin, then you would have to do the following replace

string(26): "thisemail@doesntexist.com"

with

string(25): "thisemail@doesntexist.co"

It is a pain because you have to actually change the number to change the variable which means you have to count characters somehow which is dumb.

But... a better array exists that can be represented as TEXT in the database. Enter JSON.

To convert to an associative array to a JSON string, you just have to

<?php $json = json_encode($array); ?>

To extract to an associative array

<?php $array = json_decode($json, 1); ?>

This is a very powerful way to move data around. It also minimises processing.

The alternative more common way to store information is to setup field by field. But when you do that, you the amount of conversion you have to do is orders of magnitude higher. So putting everything in a JSON field can easily cut the dev time in half or better.

But in the past, the problem with storing arrays in TEXT MySQL fields was that you could not query into them. This limits how you report on things.

But the addition of JSON support to MySQL solves this. Now you can query inside the JSON field.

MySQL does not support Indexing inside JSON tables. And I don't think it needs to. Exposing keys you would join on still makes good sense from a development standpoint to easily pick out data through PhpMyAdmin. That is why I say add the fields you want to join on outside of the JSON (even if it makes them redundant) for usability and rapid development. Then you index these fields and your reporting will be blistering fast.

So... when does this come in handy? It works amazing on data where you want the user to be able to enter anything at all. No restrictions on type checking or boundaries.

I have also been able to save security settings by generating configurations through more authenticated users. Such that an authenticated user can save a configuration of permissions they are allowed to grant to a user.

I was able to come up with a use case where a customer is taken through a series of steps. Each step they have to save information. Since all the steps behaved the same way (save POST data to JSON field) I was able to able to do all the processing on all 9 steps and load the entire object with all 9 steps in their current state with 50 lines of code.

So all the dev focus is really on the UI/UX. It is working quite well.

Posted In:

Problem Solving

ABOUT THE AUTHOR:
Software Developer always striving to be better. Learn from others' mistakes, learn by doing, fail fast, maximize productivity, and really think hard about good defaults. Computer developers have the power to add an entire infinite dimension with a single Int (or maybe BigInt). The least we can do with that power is be creative.

Other Categories