MySQL:
JSON_SCHEMA_VALID
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).
URL: https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html
Example
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> },
'> "required": ["latitude", "longitude"]
'>}';
Query OK, 0 rows affected (0.01 sec)
mysql> SET @document = '{
'> "latitude": 63.444697,
'> "longitude": 10.445118
'>}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> }
'>}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE geo (
-> coordinate JSON,
-> CHECK(
-> JSON_SCHEMA_VALID(
-> '{
'> "type":"object",
'> "properties":{
'> "latitude":{"type":"number", "minimum":-90, "maximum":90},
'> "longitude":{"type":"number", "minimum":-180, "maximum":180}
'> },
'> "required": ["latitude", "longitude"]
'> }',
-> coordinate
-> )
-> )
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> SET @point1 = '{"latitude":59, "longitude":18}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @point2 = '{"latitude":91, "longitude":0}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @point3 = '{"longitude":120}';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO geo VALUES(@point1);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO geo VALUES(@point2);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Error
Code: 3934
Message: The JSON document location '#/latitude' failed requirement 'maximum' at
JSON Schema location '#/properties/latitude'.
*************************** 2. row ***************************
Level: Error
Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)
mysql> INSERT INTO geo VALUES(@point3);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Error
Code: 3934
Message: The JSON document location '#' failed requirement 'required' at JSON
Schema location '#'.
*************************** 2. row ***************************
Level: Error
Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"');
+---------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"') |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
1 row in set (0.04 sec)