SQL, JSON and embedded JSON
No answers today, only questions.
My application is using JSON objects to store data. Initially I didn’t like them because they felt kind of sloppy; you didn’t know if the things inside were defined, were arrays, hashes, functions or what. Now, I really like this flexibility (and, yes, I know JS doesn’t have a hash).
So, how do I store this complex object in my database? So far, it hasn’t been much of an issue. The object is a list, the list has values in JSON and so the object is a table in SQL and the values are records. No big deal.
But, now I have more complex objects. They are essentially tables within tables within tables. So, the translation code can get extensive. And, I have way too many joins.
One thing I tried is to Base64 encode the JSON object and just stick it in the database. This actually works pretty well, except that I can’t get to the data with a simple SQL query. I first have to get the blob, decode it, convert it into something useable and then do processing on that. Conceptually, no so hard, but a huge pain when much of your data is hidden away like this.
My answer for now is to simplify my application so I don’t have this problem. But, it isn’t gone, just deferred. If anyone has advice, I would love to hear it.
What you’re basically describing is an object-oriented database, but using JSON to represent the object. For the past 7 years, I’ve been building sites using a PHP framework where all database records are serialized PHP objects.
There’s logic in my MySQL driver to extract fields from an object being saved into individual MySQL columns, but this is optional. By default, the entire object is serialized and stored into a single column. To improve performance, SELECT queries automatically optimize to use the extracted fields. This happens automatically.
My database drivers are based on query objects, so I’m not writing raw SQL queries in my code. I’m merely building a query by calling methods on the query object.
Here’s a pseudo example that queries a MySQL table for rows where “field” match “value”.
$q = new MySQL_Query('DataType');
$q->addFilter('field', '=', 'value');
$q->exec();
If the “field” was extracted, it would become part of the SQL query statement. If it wasn’t, then all records would be retrieved, and one-by-one, the “field” would be checked with the PHP-based driver. Far slower for large tables and consumes a lot of memory.