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.


2 comments so far

  1. Ted Wood on

    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');

    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.

  2. Ted Wood on

    To the blog author: if you can fix my post, some of my example code was dropped… email me if you need.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: