Storing JSON in an msSQL database?

Question

I'm developing a form generator, and wondering if it would be bad mojo to store JSON in an SQL database?

I want to keep my database & tables simple, so I was going to have

`pKey, formTitle, formJSON` 

on a table, and then store

{["firstName":{"required":"true","type":"text"},"lastName":{"required":"true","type":"text"}} 

in formJSON.

Any input is appreciated.

Answer 1

I use JSON extensively in my CMS (which hosts about 110 sites) and I find the speed of access data to be very fast. I was surprised that there wasn't more speed degradation. Every object in the CMS (Page, Layout, List, Topic, etc) has an NVARCHAR(MAX) column called JSONConfiguration. My ORM tool knows to look for that column and reconstitute it as an object if needed. Or, depending on the situation, I will just pass it to the client for jQuery or Ext JS to process.

As for readability / maintainability of my code, you might say it's improved because I now have classes that represent a lot of the JSON objects stored in the DB.

I used JSON.net for all serialization / deserialization. https://www.newtonsoft.com/json

I also use a single query to return meta-JSON with the actual data. As in the case of Ext JS, I have queries that return both the structure of the Ext JS object as well as the data the object will need. This cuts out one post back / SQL round trip.

I was also surprised at how fast the code was to parse a list of JSON objects and map them into a DataTable object that I then handed to a GridView.

The only downside I've seen to using JSON is indexing. If you have a property of the JSON you need to search, then you have to store it as a separate column.

There are JSON DB's out there that might server your needs better: CouchDB, MongoDB, and Cassandra.

Answer 2

A brilliant way to make an object database from sql server. I do this for all config objects and everything else that doesn't need any specific querying. extending your object - easy, just create a new property in your class and init with default value. Don't need a property any more? Just delete it in the class. Easy roll out, easy upgrade. Not suitable for all objects, but if you extract any prop you need to index on - keep using it. Very modern way of using sql server.