Archive for August, 2009|Monthly archive page

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.