Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27128

Implement JSON Schema Validation FUNCTION

Details

    Description

      A great addition to MariaDB would be able to validate JSON schemas. MySQL already have this implemented as follows:

      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).

      https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html#function_json-schema-valid

      Since MariaDB is great and it allows the use of UDFs and deterministic functions on constraints something like this would be awesome:

      CREATE TABLE t1 (a JSON CHECK JSON_SCHEMA_VALID(schema, a));

      or

      CREATE TABLE t1 (
      		 
      a JSON,
      		 
      CONSTRAINT json_validation CHECK JSON_SCHEMA_VALID(schema, a)
      		 
      );

      The word schema should be replaced by a string representing a valid JSON schema.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30287 JSON_SCHEMA_VALID returns incorrect result for type=number

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30677 Incorrect result for "SELECT JSON_SCHEMA_VALID('{}', NULL)"

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30689 JSON_SCHEMA_VALID for type=array return 1 for any string that starts with '['

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30690 Server crashed on function JSON_SCHEMA_VALID with incorrect input json schema

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30703 JSON_SCHEMA_VALID : Enum array must have at least one value

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30704 JSON_SCHEMA_VALID: multipleOf must be greater than zero

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30705 JSON_SCHEMA_VALID: schema with multipleOf for big value always return 1

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30976 Assertion `(length % 4) == 0' failed in my_lengthsp_utf32

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30977 Additional key values are not validating properly when using unevaluatedProperties with properties declared in subschemas

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30995 JSON_SCHEMA_VALID is not validating case sensitive when using regex

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31009 JSON_SCHEMA_VALID does not properly validate the maximum/minimum value of a number

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31032 UBSAN|downcast of address X which does not point to an object of type 'Item_string' in sql/json_schema.cc

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31599 Assertion `0' failed in Item_param::can_return_value from Item::val_json, UBSAN: member access within null pointer of type 'struct String' in sql/item_jsonfunc.cc

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          duplicates

          Task - A task that needs to be done. MDEV-25934 JSON-Schema as Contraint on JSON-column

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          is duplicated by

          Task - A task that needs to be done. MDEV-27815 Implement JSON_SCHEMA_VALID()

          • Major - Major loss of function.
          • Closed
          relates to

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-17397 Implement JSON functions available in MySQL 8.0 but not MariaDB

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-30219 Add more validation keywords for json schema

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33640 Server crashes at my_hash_free

          • Major - Major loss of function.
          • Confirmed

          Activity

            Ascending order - Click to sort in descending order
            View 7 older comments
            rucha174 Rucha Deodhar added a comment - - edited

            lstartseva Thanks for the comment. You're right. I've fixed it as part of below patch

            1) https://github.com/MariaDB/server/commit/357c208d67fa90b79da013c6cb7fae044af8c1b6
            2), 3) and 4): https://github.com/MariaDB/server/commit/0058ac37d31767686cea2816f17b30fe59b6f60d

            rucha174 Rucha Deodhar added a comment - - edited lstartseva Thanks for the comment. You're right. I've fixed it as part of below patch 1) https://github.com/MariaDB/server/commit/357c208d67fa90b79da013c6cb7fae044af8c1b6 2), 3) and 4): https://github.com/MariaDB/server/commit/0058ac37d31767686cea2816f17b30fe59b6f60d
            holyfoot Alexey Botchkov added a comment -

            Comments on slack.
            After these addressed i'm ok with making the preview out of this.

            holyfoot Alexey Botchkov added a comment - Comments on slack. After these addressed i'm ok with making the preview out of this.
            rucha174 Rucha Deodhar added a comment -

            ( Reassigned after working on review 2 for final check )

            rucha174 Rucha Deodhar added a comment - ( Reassigned after working on review 2 for final check )
            holyfoot Alexey Botchkov added a comment -

            commit 70a7de2aed7677ef9e0a14f5734150cbc58023ae
            comments on slack.
            With at least keyword_map= &empty_map fix i'm ok with the patch to preview.

            holyfoot Alexey Botchkov added a comment - commit 70a7de2aed7677ef9e0a14f5734150cbc58023ae comments on slack. With at least keyword_map= &empty_map fix i'm ok with the patch to preview.
            ramesh Ramesh Sivaraman added a comment -

            ok to push

            ramesh Ramesh Sivaraman added a comment - ok to push

            People

              rucha174 Rucha Deodhar
              rubenferreira7575 Rúben Ferreira
              Votes:
              4 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.