Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Storing JSON in a Database

 
Joe Harry
Ranch Hand
Posts: 10128
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We have currently something similar (but with XML instead of JSON) and we store it in a NVARCHAR(MAX) column (using Microsoft SQL Server)
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Srikkanth Mohanasundaram
Ranch Hand
Posts: 243
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I will never, ever, under any circumstances, make someone query the JSON structure.


I want to add one more thing to it.

I will never,ever, change the properties in the JSON object in the future ( Migration headaches )
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65337
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Srikkanth Mohanasundaram wrote:I will never,ever, change the properties in the JSON object in the future ( Migration headaches )

I would rethink this one. Things change. Business needs change.

I would use a versioning scheme so that changes can be handled in a deterministic manner.
 
Srikkanth Mohanasundaram
Ranch Hand
Posts: 243
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Things change. Business needs change.

Completely agree : )

Thanks
Srikkanth
 
Joe Harry
Ranch Hand
Posts: 10128
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So what do you guys say? A CLOB should be fine?
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
CLOB is fine IMO.
And just for information, MongoDB stored all data in JSON format and of course it is not a relational DB.
 
Joe Harry
Ranch Hand
Posts: 10128
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know and I have worked with MongoDB before, but we do not have it in our landscape and I doubt we would get that. So I have to live with an RDBMS!
 
Joe Harry
Ranch Hand
Posts: 10128
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess Postgres SQL does not have a CLOB, but rather a text field which is unlimited in size. So I guess I would just go for this option.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe Harry wrote:I guess Postgres SQL does not have a CLOB, but rather a text field which is unlimited in size.


Yes it does not have CLOB, but it has a "json" data type. I guess it is included in 9.2 version. Check if your version has json as data type.
 
Joe Harry
Ranch Hand
Posts: 10128
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Cool! I will check that!
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PostgreSQL has had a JSON data-type since v.9.2, and later versions provide more functions to manipulate it. I haven't used it myself, but here's a quick overview.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic