Is there a best practice or whatsoever governing storing JSON in a database? I understand that it depends on what I would do with the JSON. Let us say I have a config file that is represented as a JSON. There might be multiple such JSON files representing different configurations. I would now need to store them and retrieve them, modify them and later save the modifications back. I would not be querying the JSON structure, but rather I will pick up the complete JSON config based on an ID. Currently, I'm planning to store that JSON as a CLOB. Is this a good approach? What do you guys suggest?
SCJP 1.4, SCWCD 1.4 - Hints for you, Certified Scrum Master
Did a rm -R / to find out that I lost my entire Linux installation!
First of all, get your manager, your manager's manager, your manager's manager's manager, etc all the way to the top, and have them all make an oath:
I will never, ever, under any circumstances, make someone query the JSON structure.
If you manage to have it notarized, archived and carved on a monument erected just outside of your office, you're moderately safe to proceed.
Seriously though, CLOB looks good to me, you could hit your database's VARCHAR limit with a more complicated JSONs (look up the limits for your database). If encoding is an issue (can still happen nowadays), consider using NCLOB.
I would use a versioning scheme so that changes can be handled in a deterministic manner.
Been there done that : ) We had to create a migration implementation for every release, it wasn't a pleasant experience ; ) The client for some reason didn't want to run the migration job to update the database for every release and we had to update the JSON when the user logs on to the application, it was really crazy.
Let's say the user saved the JSON version v1, and didn't log on for a few months, and the application code has progressed to v4, we still had to support the migration from v1 to v4. So we had (n-1) migration implementations in the code base.
Incidentally, I agree with Martin about making this a strictly black-box store.
Your config file is obviously used by something (outside the database) which depends on the structure and contents of the JSON. That's one set of dependencies i.e. JSON file structure <--> config user. If you simply treat the DB as a dumb store (sigh...), as you've suggested you will, then you can get away with this because the DB never needs to know anything about the contents of the JSON so there are no extra dependencies.
But if you ever decide to introduce some kind of query operation that inspects the JSON structure in the database, you suddenly have another set of dependencies i.e. DB query functions <--> JSON-in-the-DB, independently from the JSON file. Plus, as Srikkanth mentioned above, you will also have problems with any changes in versions e.g. if v2 of the JSON has different structure from v1, how do you manage your query functionality which has to look at data in v1 and v2 formats? None of this is impossible, of course, but they will need to understand that there is a cost involved in dealing with this extra complexity.
So make sure your bosses understand that just because "it's in the database", they still can't treat this JSON like it was really stored in a structured fashion in the database i.e. no clever queries or updates, just a dumb CLOB.
Times ago, I considered to store a structure similar to a bill of materials into a CLOB column. To be honest, my objective was to save space on table - instead of having a lot of rows, I thought t save only structure key and the payload as JSON data.Unluckily, my target database has not built in compression capabilities, so eventually I gave up, since I did not want to deal with hand made compression.
That which doesn't kill us makes us stronger. I think a piece of pie wouldn't kill me. Tiny ad: