I was dealing with dozens of external API’s that return hundreds of data points yesterday. I wanted to store all the retrieved data, but creating a column for each piece of data was going to be overkill since most of the data will not be utilized often.
I created a JSON column in MariaDB (version 10), stringified the object and stored in a single column. I can then retrieve that string, and parse back into a JSON object. Makes for very easy loading into a datastore and saving back to the database without creating hundreds of indexed inputs. That was win number 1.
But I also wanted to search and index on those nested data points when retrieving records.
In come virtual columns that automatically extract elements from within the JSON field. I’ve use virtual columns a lot, but extracting from JSON is a real game changer for my db structure going forward.
Consider a mariadb table cars with a column named car_data that is a JSON data type (which is an alias for LONGTEXT).
If I store a valid JSON string in car_data, I can then create virtual columns (columns you cannot modify data directly) for some elements that I want to search on, or retrieve without having to parse the json every time.
ALTER TABLE cars ADD door_count INT(2) AS (JSON_VALUE(car_data, '$.DoorsTotal'));
The above is modifying the cars table, and creates a column door_count which automatically extracts the value for DoorsTotal from the JSON. Rinse, repeat for the pieces of data you want to pull to front from your JSON data.
Now, I can search, index, and retrieve door_count through a stand wappler query!
For me, this was a real eye-opener advancement that will provide for a very clean data structure and speed development, which ultimately saves my clients money!