Thanks Phil I'm a big fan of your articles. I completely agree JSON can be a key element of efficient application data access. As far as data validation goes tho... imo and afaik Sql Server's current JSON features set is quite complete. Of course, this could be legitimately disagreed with and for sure I'm extremely interested in counterarguments.
Are the following validation steps [applied in this order] sufficient to reasonably ensure data integrity? App sends json document to SQL then:
1) is the JSON document valid? -- ISJSON() function --> if yes, proceed to deserialize and parse the document
2) are the correct data elements present? -- STRICT optional parameter(s) to Json parse functions --> parse data elements
3) can the data be persisted? -- DB Engine --> DB Engine DML
Whether or not the document is valid compared to its own schema doesn't really enter the picture. It would be extra information. Are the others listed "Python, Go, Java, PHP, .NET. Even MongoDB.." transaction engines? Maybe Mongo I don't know.
If Sql Server could generate a JSON schema for any query that uses FOR JSON that would be amazing. I still refer to (and use some modified code from) this article you wrote about 2 years ago:
Microsoft could expand on this and make the functionality built in. That would be great schema support I'd love to see.