Version sans cache

Mise en cache désactivé. Réglage défaut pour cette page : actif (code DEF204)
Si l'affichage est trop lent, vous pouvez désactiver le mode utilisateur pour visualiser la version en cache.

Rechercher dans le manuel MySQL

12.17.7 JSON Schema Validation Functions

Table des matières

Beginning with MySQL 8.0.17, MySQL supports validation of JSON documents against JSON schemas conforming to Draft 4 of the JSON Schema specification. This can be done using either of the functions detailed in this section, both of which take two orguments, a JSON schema, and a JSON document which is validated against the schema. JSON_SCHEMA_VALID() returns true if the document is validates against the schema, and false if it is not; JSON_SCHEMA_VALIDATION_REPORT() provides a report in JSON format on the validation.

Both functions handle null or invalid input as follows:

  • If at least one of the arguments is NULL, the function returns NULL.

  • If at least one of the arguments is not valid JSON, the function raises an error (ER_INVALID_TYPE_FOR_JSON)

  • In addition, if the schema is not a valid JSON object, the function returns ER_INVALID_JSON_TYPE.

MySQL supports the required attribute in JSON schemas to enforce the inclusion of required properties (see the examples in the function descriptions).

MySQL does not support external resources in JSON schemas; using the $ref keyword causes JSON_SCHEMA_VALID() to fail with ER_NOT_SUPPORTED_YET.

Note

MySQL supports regular expression patterns in JSON schema, which supports but silently ignores invalid patterns (see the description of JSON_SCHEMA_VALID() for an example).

These functions are described in detail in the following list:

  • JSON_SCHEMA_VALID(schema,document)

    Validates a JSON document against a JSON schema. Both schema and document are required. The schema must be a valid JSON object; the document must be a valid JSON document. Provided that these conditions are met: If the document validates against the schema, the function returns true (1); otherwise, it returns false (0).

    In this example, we set a user variable @schema to the value of a a JSON schema for geographical coordinates, and another one @document to the value of a JSON document containing one such coordinate. We then verify that @document validates according to @schema by using them as the arguments to JSON_SCHEMA_VALID():

    1. mysql> SET @schema = '{
    2.    '>  "id": "http://json-schema.org/geo",
    3.     '> "$schema": "http://json-schema.org/draft-04/schema#",
    4.    '> "description": "A geographical coordinate",
    5.     '> "type": "object",
    6.    '> "properties": {
    7.     '>   "latitude": {
    8.    '>     "type": "number",
    9.     '>     "minimum": -90,
    10.    '>     "maximum": 90
    11.     '>   },
    12.    '>   "longitude": {
    13.     '>     "type": "number",
    14.    '>     "minimum": -180,
    15.     '>     "maximum": 180
    16.    '>   }
    17.     '> },
    18.    '> "required": ["latitude", "longitude"]
    19.     '>}';
    20. Query OK, 0 rows affected (0.01 sec)
    21.  
    22.  
    23. mysql> SET @document = '{
    24.    '> "latitude": 63.444697,
    25.     '> "longitude": 10.445118
    26.    '>}';
    27. Query OK, 0 rows affected (0.00 sec)
    28.  
    29.  
    30. mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
    31. +---------------------------------------+
    32. | JSON_SCHEMA_VALID(@schema, @document) |
    33. +---------------------------------------+
    34. |                                     1 |
    35. +---------------------------------------+
    36. 1 row in set (0.00 sec)

    Since @schema contains the required attribute, we can set @document to a value that is otherwise valid but does not contain the required properties, then test it against @schema, like this:

    1. mysql> SET @document = '{}';
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
    5. +---------------------------------------+
    6. | JSON_SCHEMA_VALID(@schema, @document) |
    7. +---------------------------------------+
    8. |                                     0 |
    9. +---------------------------------------+
    10. 1 row in set (0.00 sec)

    If we now set the value of @schema to the same JSON schema but without the required attribute, @document validates because it is a valid JSON object, even though it contains no properties, as shown here:

    1. mysql> SET @schema = '{
    2.    '> "id": "http://json-schema.org/geo",
    3.     '> "$schema": "http://json-schema.org/draft-04/schema#",
    4.    '> "description": "A geographical coordinate",
    5.     '> "type": "object",
    6.    '> "properties": {
    7.     '>   "latitude": {
    8.    '>     "type": "number",
    9.     '>     "minimum": -90,
    10.    '>     "maximum": 90
    11.     '>   },
    12.    '>   "longitude": {
    13.     '>     "type": "number",
    14.    '>     "minimum": -180,
    15.     '>     "maximum": 180
    16.    '>   }
    17.     '> }
    18.    '>}';
    19. Query OK, 0 rows affected (0.00 sec)
    20.  
    21.  
    22. mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
    23. +---------------------------------------+
    24. | JSON_SCHEMA_VALID(@schema, @document) |
    25. +---------------------------------------+
    26. |                                     1 |
    27. +---------------------------------------+
    28. 1 row in set (0.00 sec)

    JSON Schema has support for specifying regular expression patterns for strings, but the implementation used by MySQL silently ignores invalid patterns. This means that JSON_SCHEMA_VALID() can return true even when a regular expression pattern is invalid, as shown here:

    1. mysql> SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"');
    2. +---------------------------------------------------------------+
    3. | JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"') |
    4. +---------------------------------------------------------------+
    5. |                                                             1 |
    6. +---------------------------------------------------------------+
    7. 1 row in set (0.04 sec)

  • JSON_SCHEMA_VALIDATION_REPORT(schema,document)

    Validates a JSON document against a JSON schema. Both schema and document are required. As with JSON_VALID_SCHEMA(), the schema must be a valid JSON object, and the document must be a valid JSON document. Provided that these conditions are met, the function returns a report, as a JSON document, on the outcome of the validation. If the JSON document is considered valid according to the JSON Schema, the function returns a JSON object with one property valid having the value "true". If the JSON document fails validation, the function returns a JSON object which includes the properties listed here:

    • valid: Always "false" for a failed schema validation

    • reason: A human-readable string containing the reason for the failure

    • schema-location: A JSON pointer URI fragment identifier indicating where in the JSON schema the validation failed (see Note following this list)

    • document-location: A JSON pointer URI fragment identifier indicating where in the JSON document the validation failed (see Note following this list)

    • schema-failed-keyword: A string containing the name of the keyword or property in the JSON schema that was violated

    Note

    JSON pointer URI fragment identifiers are defined in RFC 6901 - JavaScript Object Notation (JSON) Pointer. (These are not the same as the JSON path notation used by JSON_EXTRACT() and other MySQL JSON functions.) In this notation, # represents the entire document, and #/myprop represents the portion of the document included in the top-level property named myprop. See the specification just cited and the examples shown later in this section for more information.

    In this example, we set a user variable @schema to the value of a a JSON schema for geographical coordinates, and another one @document to the value of a JSON document containing one such coordinate. We then verify that @document validates according to @schema by using them as the arguments to JSON_SCHEMA_VALIDATION_REORT():

    1. mysql> SET @schema = '{
    2.    '>  "id": "http://json-schema.org/geo",
    3.     '> "$schema": "http://json-schema.org/draft-04/schema#",
    4.    '> "description": "A geographical coordinate",
    5.     '> "type": "object",
    6.    '> "properties": {
    7.     '>   "latitude": {
    8.    '>     "type": "number",
    9.     '>     "minimum": -90,
    10.    '>     "maximum": 90
    11.     '>   },
    12.    '>   "longitude": {
    13.     '>     "type": "number",
    14.    '>     "minimum": -180,
    15.     '>     "maximum": 180
    16.    '>   }
    17.     '> },
    18.    '> "required": ["latitude", "longitude"]
    19.     '>}';
    20. Query OK, 0 rows affected (0.01 sec)
    21.  
    22. mysql> SET @document = '{
    23.    '> "latitude": 63.444697,
    24.     '> "longitude": 10.445118
    25.    '>}';
    26. Query OK, 0 rows affected (0.00 sec)
    27.  
    28. mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
    29. +---------------------------------------------------+
    30. | JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
    31. +---------------------------------------------------+
    32. | {"valid": true}                                   |
    33. +---------------------------------------------------+
    34. 1 row in set (0.00 sec)

    Now we set @document such that it specifies an illegal value for one of its properties, like this:

    1. mysql> SET @document = '{
    2.    '> "latitude": 63.444697,
    3.     '> "longitude": 310.445118
    4.    '> }';

    Validation of @document now fails when tested with JSON_SCHEMA_VALIDATION_REPORT(). The output from the function call contains detailed information about the failure (with the function wrapped by JSON_PRETTY() to provide better formatting), as shown here:

    1. mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
    2. *************************** 1. row ***************************
    3. JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
    4.   "valid": false,
    5.   "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
    6.   "schema-location": "#/properties/longitude",
    7.   "document-location": "#/longitude",
    8.   "schema-failed-keyword": "maximum"
    9. }
    10. 1 row in set (0.00 sec)

    Since @schema contains the required attribute, we can set @document to a value that is otherwise valid but does not contain the required properties, then test it against @schema. The output of JSON_SCHEMA_VALIDATION_REPORT() shows that validation fails due to lack of a required element, like this:

    1. mysql> SET @document = '{}';
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
    5. *************************** 1. row ***************************
    6. JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
    7.   "valid": false,
    8.   "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
    9.   "schema-location": "#",
    10.   "document-location": "#",
    11.   "schema-failed-keyword": "required"
    12. }
    13. 1 row in set (0.00 sec)

    If we now set the value of @schema to the same JSON schema but without the required attribute, @document validates because it is a valid JSON object, even though it contains no properties, as shown here:

    1. mysql> SET @schema = '{
    2.    '> "id": "http://json-schema.org/geo",
    3.     '> "$schema": "http://json-schema.org/draft-04/schema#",
    4.    '> "description": "A geographical coordinate",
    5.     '> "type": "object",
    6.    '> "properties": {
    7.     '>   "latitude": {
    8.    '>     "type": "number",
    9.     '>     "minimum": -90,
    10.    '>     "maximum": 90
    11.     '>   },
    12.    '>   "longitude": {
    13.     '>     "type": "number",
    14.    '>     "minimum": -180,
    15.     '>     "maximum": 180
    16.    '>   }
    17.     '> }
    18.    '>}';
    19. Query OK, 0 rows affected (0.00 sec)
    20.  
    21. mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
    22. +---------------------------------------------------+
    23. | JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
    24. +---------------------------------------------------+
    25. | {"valid": true}                                   |
    26. +---------------------------------------------------+
    27. 1 row in set (0.00 sec)

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-json-validation-functions.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut