Store JSON in an sqlite field?

Question

I'm writing an application that communicates with a web API, which responds with JSON. Currently, I'm translating the JSON objects to Java objects using gson (which is awesome, by the way).

Now, I want to store some of these objects in an SQLite database. However, they have lots of properties that would never be used in queries (i.e. I won't be ORDERing, WHEREing, or anything like that with those properties), so I feel it's unnecessary to create columns for all of them. What I'm thinking of doing is:

• Only have columns for the essential data that will be used when querying the database
• Have one TEXT or BLOB column (which one do you recommend?) that stores the actual JSON, so I can recreate my Java object from it and access all the data.

This would both make my life easier and streamline my code (I would not have to write very different code when dealing with data from the API vs. data from the database).

However, although I see no downsides, it feels a bit fishy.

What kind of trouble do you think I would run into if I use this technique?

Answer 1

The main thing I wouldn't like about it is relying on the structure of the stored/retrieved JSON to be valid, since it's completely out of the hands of the database. Not that you can't take precautions against possible issues, but if the JSON is somehow truncated or otherwise compromised in a way that trips up the parser, you're then missing the entire object instead of just one invalid or truncated property. If that's an acceptable risk, then it's probably a reasonable technique.