I had for my study goal to review the JSON implementation in SQL Server 2016. The implementation is simple, only interacting with data, or properties, of JSON objects. Unlike XML, JSON has the ability to contain functions as well as data, because JSON is simply serialization of JavaScript objects. In JavaScript, even functions are objects.
I am both pleased and disappointed. I have not had time to test the JSON feature yet, and wonder how it handles true JSON with integrated methods. I’m guessing functions or closures are simply stored as strings. Does anyone have feedback on that issue? Regardless, I’m pleased that functions in JSON objects are not supported in SQL Server, due to the security issues that can arise. It’s just too dangerous, as we found with VB script in office products.
So, now JSON in SQL is simply data transfer objects that may be passed over HTML, or anything else, in the form of a string. The string can be parsed into individual, strongly data types, and handled as a set of data, just like other forms of collections.
My fear is that developers are now going to turn SQL Server into an ORM engine. They are going to start passing large sets of data, or even parameters, to SQL Server in the form of JSON objects. This was a very popular technique with XML that got a lot of people in trouble. Passing a BLOB of data in as a string to a stored procedure is easy to do, when you write it. But, when it comes to maintaining what is going on, you just created a horrible thing. It is harder to test than using truly typed objects.
Since SQL Server can now read from JSON, query against JSON, and convert SQL Data to JSON, we now have the opportunity for developers to ignore the dissonance between object oriented data and relational data. I predict we will see corruption of normalized databases, because we want our schema to match data that is available in our business layer. I also anticipate a lot of performance and maintenance issues resulting from bypassing strongly typed designs with an ORM interface.
I have seen this technique used with XML. Instead of merging data changes in the database, the existing data is simply deleted, and the new data is inserted, based on the values received from the user interface. This is not a requirement of passing XML to a stored procedure; I just keep seeing this pattern over and over, because it is a quick, easy way to get things working. Well, it doesn’t work in any mature system, or a system requiring auditing, as that required by many regulatory laws. If an update is implemented by a delete/insert, the audit trail becomes more difficult to follow.
In short, if you want to use JSON, use a JSON data store. That’s my take. At least if you are working in the Microsoft stack. I can see some value if you are working in linux or unix, using a JDBC driver. However, I don’t know if JDBC supports JSON to SQL Server yet. Maybe you can tell us how this works from a Java based app?
Cheers,
Ben