I've noticed that SQL Server's JSON support has changed the way I use SQL Server, particularly where the database is working closely with applications. It happened so slowly that I hadn't really been aware of it. First, I suppose, was the use of a single JSON document as an output from a procedure, giving me the ability to pass several results, along with errors, warnings and so on, in a single NVARCHAR(MAX) variable. It is all very liberating. This is great for returning data to a JavaScript-based application, as it is so much easier for them and loosens the coupling between application and database.

Then, of course, it is rather good to be able to pass a JSON document from an application to a stored procedure. Naturally, there can be all sorts of lists, arrays, tables and atomic data in this document. You'd be amazed how much easier it is to create a robust interface with an application this way.

This all works fine, as long as you don't get mistakes, such as using the wrong JSON document as a parameter. You can test out a document pretty well, but it takes away some of the new-found economy of code. You want to validate that document rigorously, check out the datatypes, and check the data, as you would with a CHECK constraint in a relational table, but do it easily without bulking out your code or having something else to test.

Once you've hit this sort of wall, you understand why JSON Schema is so good. It means that you can, from the specifications within a JSON document, validate your input, enumerate the valid alternatives, the legitimate range of a number, check whether the data is sensitive, or whether it matches a regex. It will check whether array items are unique in value and so on. All these checks are done by making a single call to a method, to validate the document to the schema.

Once you have the technology, you can provide a JSON document from the stored procedure that allows the application to validate your output. This provides a sort of data document for the data to send to the application, so that there is less need to develop database and application in close step.

JSON Schema is now becoming more mainstream, with support in all the major languages such as Python, Go, Java, PHP, .NET. Even MongoDB now has it. Does SQL Server? From the way that it supports, and used to promote, XML Schema, you'd expect it to; but it doesn't. I can test a JSON file as being appropriate for a stored procedure from within PowerShell or Python, but I want to, and need to, test it at the point of consumption, within the batch or routine that has to use the JSON parameter.

OK. The paint isn't entirely dry on the JSON Schema standard. It is in draft 7, submitted to the IETF, but then the .NET implementation is very robust. I reckon it is for Microsoft to get stuck in with a SQL function that validates a JSON document, using a JSON Schema document.