Geen cache-versie.

Caching uitgeschakeld. Standaardinstelling voor deze pagina:ingeschakeld (code DEF204)
Als het scherm te langzaam is, kunt u de gebruikersmodus uitschakelen om de cacheversie te bekijken.

Rechercher dans le manuel MySQL

12.17.7 JSON Schema Validation Functions

Inhoudsopgave

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)

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-json-validation-functions.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut