When we create or modify a table in MySQL, we have the option of applying a CHECK constraint against one or more columns. This allows us to check the data before it enters the database. Data can only enter the database if it doesn’t violate the rules in our CHECK constraint.
If the column is a JSON column, we have the option of creating a CHECK constraint that checks that the JSON document doesn’t violate its JSON schema. To do this, we can include the schema in the CHECK constraint.
MySQL provides us with a bunch of functions and operators that allow us to search JSON documents for various things, such as specific values, paths, keys, etc.
Here are seven functions and three operators that search JSON documents in MySQL.
Continue readingMySQL provides us with a bunch of functions that can modify JSON values. Here are ten functions that we can use to modify JSON values in MySQL.
Continue readingIn MySQL, we have several options when it comes to extracting data from JSON documents. Below are six methods we can use to do this.
Continue readingMySQL includes some inbuilt functions that can be used to create JSON values, such as JSON arrays, JSON objects, or a quoted string literal.
Here are seven functions that allow us to create JSON values in MySQL.
Continue readingBelow is a list of the JSON functions and operators available in MySQL.
Continue readingIn MySQL, we have several options when it comes to removing the quotes from a JSON value.
For example, normally if we return a string value from a JSON document, we would get that value along with its quotes. But sometimes we might not want the quotes. Below are three methods we can use to unquote a JSON value in MySQL.
Continue readingIn MySQL, the JSON_SCHEMA_VALIDATION_REPORT() function validates a JSON document against a JSON schema and returns a report of the outcome of that validation. The report is returned as a JSON document.
If the document is valid, only one field is returned: "valid" : true. If the document is not valid, a more extensive report is provided.
In MySQL, the JSON_SCHEMA_VALID() function validates a JSON document against a JSON schema. It returns true (1) if the document is valid, and false (0) if it is not.
In MySQL, we can use the ->> operator to extract a value from a JSON document, and unquote that value.
We provide the column that contains the JSON document to the left of the operator, and the path to the value that we want to extract to the right.
The ->> operator is equivalent to JSON_UNQUOTE(JSON_EXTRACT()), and it provides the same results.