Much like SQL Server embraced native storage of XML data, Postgresql has embraced native storage of JSON. The benefits and detriments are identical. It can be used and/or abused.
JSON is much more popular with the Java community than XML data notation. It is not as easy to read as XML, but remains very flexible. More significant, is the fact that many of the libraries that have been made even more popular in HTML 5, such as Angular or Knockout, fully embrace the structure of JSON.
So, now you have a database that allows you to store your JSON data in a native format. You are not simply storing a JSON formatted string in a variable string column. The database engine is aware and responds to the JSON being stored.
Postgresql has two modes for storing the JSON data. JSON stores the data in a near string form, but still requires it to be valid JSON syntax. JSONb stores the data as JSON as well. In addition, it converts it into a binary form resulting in fixed position of data in arrays, etc. JSONb also allows indexing,resulting in quicker searches in the JSON data, much like using xpath in SQL Server.
While I think this is a really cool achievement, I wonder if it is a good use of your database engine? I have always had the same opinion about storing XML in SQL Server. JSON and XML may be readily parsed outside of your database storage. There are other engines tuned for this kind of data, and they scale a lot easier than an SQL engine. This is a gray line for me; I could be convinced to use them in a SQL engine under the right circumstances.
Are you storing non-relational data in your SQL engines? How do you handle the bloat? What are the benefits you have seen? Is it worth the cost? Share your experience in our comments, or drop me an email at btaylor@sswug.org.
Cheers,
Ben