{"id":12222,"date":"2023-11-09T03:14:01","date_gmt":"2023-11-09T10:14:01","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12222"},"modified":"2023-11-09T03:25:46","modified_gmt":"2023-11-09T10:25:46","slug":"mysql-json_schema_validation_report","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_schema_validation_report\/","title":{"rendered":"MySQL JSON_SCHEMA_VALIDATION_REPORT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_SCHEMA_VALIDATION_REPORT()<\/code> function to validate JSON documents against a specified schema.<\/p>\n\n\n\n

Introduction to MySQL JSON_SCHEMA_VALIDATION_REPORT() function<\/h2>\n\n\n\n

The JSON_SCHEMA_VALIDATION_REPORT()<\/code> function validates JSON documents against a specified JSON schema and provides a detailed report on validation failures.<\/p>\n\n\n\n

Here’s the syntax of the JSON_SCHEMA_VALIDATION_REPORT()<\/code> function:<\/p>\n\n\n

JSON_SCHEMA_VALIDATION_REPORT(json_schema, json_document)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
    \n
  • json_schema<\/code>: The JSON schema against which you want to validate the JSON document.<\/li>\n\n\n\n
  • json_document<\/code>: The JSON document that you want to validate.<\/li>\n<\/ul>\n\n\n\n
    If the JSON document is valid based on the JSON Schema, the JSON_SCHEMA_VALIDATION_REPORT()<\/code> function returns a JSON object with one property\u00a0valid<\/code>\u00a0having the value true<\/code>. <\/p>\n\n\n\n
    If the JSON document fails validation, the JSON_SCHEMA_VALIDATION_REPORT()<\/code> function returns a JSON object that includes the following properties:<\/p>\n\n\n\n
      \n
    • valid<\/code>:  with the value false<\/code>.<\/li>\n\n\n\n
    • reason<\/code>: contains the reason for the failure.<\/li>\n\n\n\n
    • schema-location<\/code>: A JSON pointer URI fragment identifies the precise location within the JSON schema where the validation encountered an issue.<\/li>\n\n\n\n
    • document-location<\/code>: A JSON pointer URI fragment specifies the exact location within the JSON document where the validation encountered an error.<\/li>\n\n\n\n
    • schema-failed-keyword<\/code>: A string naming the specific keyword or property in the JSON schema that was violated.<\/li>\n<\/ul>\n\n\n\n
      MySQL JSON_SCHEMA_VALIDATION_REPORT() function examples<\/h2>\n\n\n\n
      Let’s take an example of using the JSON_SCHEMA_VALIDATION_REPORT()<\/code> function.<\/p>\n\n\n\n
      First, set up a JSON schema for employee details:<\/p>\n\n\n
      SET<\/span> @employee_schema = '{\n    \"type\": \"object\",\n    \"properties\": {\n        \"name\": {\"type\": \"string\"},\n        \"age\": {\"type\": \"integer\"},\n        \"department\": {\"type\": \"string\"}\n    },\n    \"required\": [\"name\", \"age\", \"department\"]\n}'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Second, validate a JSON document against the schema:<\/p>\n\n\n
      SET<\/span> @valid_employee = '{\n    \"name\": \"John\",\n    \"age\": 30,\n    \"department\": \"HR\"\n}'<\/span>;\n\nSELECT<\/span> JSON_SCHEMA_VALIDATION_REPORT(@employee_schema, @valid_employee) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      It returns a JSON object that has the valid property with the value true because the JSON document is valid according to the schema:<\/p>\n\n\n
      +-----------------+<\/span>\n| result          |\n+-----------------+<\/span>\n| {\"valid\": true} |\n+-----------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Third, use the JSON_SCHEMA_VALIDATION_REPORT()<\/code> function to validate the invalid JSON document:<\/p>\n\n\n
      SET<\/span> @invalid_employee = '{\n    \"name\": \"Alice\",\n    \"age\": \"25\",  \n    \"department\": \"IT\"\n}'<\/span>;\n\nSELECT<\/span> \n  JSON_PRETTY(\n    JSON_SCHEMA_VALIDATION_REPORT(\n      @employee_schema, @invalid_employee\n    )\n  ) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Note that we use the JSON_PRETTY()<\/code> function to pretty-print the output for better readability.<\/p>\n\n\n\n
      It returns the following output:<\/p>\n\n\n
      -------------------------------------------------------------+<\/span>\n| result                                                                                                                                                                                                                                                  |\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<\/span>\n| {\n  \"valid\": false,\n  \"reason\": \"The JSON document location '#\/age' failed requirement 'type' at JSON Schema location '#\/properties\/age'\",<\/span>\n  \"schema-location\": \"#\/properties\/age\",<\/span>\n  \"document-location\": \"#\/age\",<\/span>\n  \"schema-failed-keyword\": \"type\"\n} |\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<\/span>\n1 row in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      The output explains the reason for failure in the reason object that the type of the age is not valid according to the schema.<\/p>\n\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the JSON_SCHEMA_VALIDATION_REPORT()<\/code> function to validate JSON documents against a specified JSON schema.<\/li>\n<\/ul>\n
        \n\t
        \n\t\t
        Was this tutorial helpful? <\/div>\n\t\t
        \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
        \n\t\t
        \n\t\t\t
        <\/div>\n\t\t\t\n\t\t\t