Validating JSON

Dave Stokes - Oracle MySQL - MySQL Track Room - Percona Live 2021

JSON or JavaScript Object Notation has become the data interchange format of choice. Most relational databases have added a JSON data type (Oracle, Postgresql, MySQL) or some accommodation for JSON data (SQL Server, MariaDB). But the free form nature of JSON is problematic for relational databases resulting in compromises in speed, handling of key-value pairs, and general lack of the ability to validate data. RDMS have had the ability to check for missing values, data type checks, and range checks but that is lacking in the JSON sphere. However, JSON-Schema.org has developed a vocabulary to annotate and validate JSON documents to describe your data formats, documents your implementation, and provides a way to validate data to allow both automatic testing and assuring data quality.

The work of JSON-Schema.org is heading towards RFC status and could very well remove many of the objections to JSON data use in a relational system. We will look at whom in starting to use their methods and the progress in standardization. ∎