What are Advantages / Disadvantages of Storing Data as JSON in Database?
I'm currently experimenting with storing the data as JSON in the database. I found out that latest versions of database engines allow storing of data JSON or JSON by data type. I'm wondering what exactly are the advantages and disadvantages of storing data as JSON vs. storing it as a regular text-string?
I did try finding information about it - but could not find anything that directly answers the question I have.
PS: My dilemma is about retrieving the data as JSON vs. retrieving it as plain text string. Ultimately, JSON itself looks like a plain text string to my eyes.
JSON is good for storing objects/documents in the DB when you are not sure of the structure of the object. The beauty of this is that you can store your data hierarchically.
JSON can be a plain string, but if you parse the string (json_decode() in PHP, JSON.parse() in JS, etc), then you get an object which can be manipulated. This is catching on because these JSON objects can match directly with controllers and views.
Hope this helps.
@Prasad Ajinkya - right. So far, the only advantage I've realized is that the data obtained from the database can be directly employed as an object. So that works. Was wondering if there are any specific cases where data needs to be stored as JSON. Thank you for the reply.
- Where your object schema is unknown during design time and becomes relevant in run-time.
- Where your object schema changes over a period of time (progressive profiling, document completion, multi-layer form fills)
- Where each document/tupple is standalone and doesnt have to rely on multiple joins.
Let's say that we create a Pay Per Use REST API (The API Economy) .. and for sake of compliance and billing have to store each Request/Response objects. So a simple case could be store both the request and response objects as JSON documents within a single document, and keep a collection of those.
The thing about specifications is that if you keep specifying these, then the very reason why you would require this is eliminated. Since with those sharper specifications, you could always design an optimised RDBMS.