In 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.
A common task when working with numbers is to format them to a certain amount of decimal places. Two decimal places seems to be the most common format, but we can format to any number of decimal places we want. MySQL provides us with a number of functions that can help us achieve this.
Below are seven functions that can be used to format a number to two decimal places in MySQL.
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.
When using a function like CONVERT_TZ() to convert between time zones in MySQL, we provide the date/time value, along with the original time zone and the destination time zone (i.e. the time zone that we’re converting to).
One typical way to do this is to use the time zone offset, such as −05:00 to specify the time zone. Another method is to use the time zone name. However, this requires that we have configured named time zones in MySQL.
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.
In MySQL, we can use the -> operator to extract data from a JSON document.
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 an alias for the two-argument form of the JSON_EXTRACT() function.
In MySQL, the MEMBER OF() operator tests whether or not a value is a member of a given JSON array.
It returns true (1) if the array contains the value, and false (0) if it doesn’t.
The MEMBER OF() operator was added in MySQL 8.0.17.
In MySQL, JSON_OVERLAPS() is a function that tests whether or not two JSON documents have any key-value pairs or array elements in common.
The function returns true (1) if the documents have any key-value pairs or array elements in common, and false (0) if they don’t.
The JSON_OVERLAPS() function was added in MySQL 8.0.17.
In MySQL, JSON_MERGE() is a deprecated function that merges two or more JSON documents and returns the result.
It was deprecated in MySQL 8.0.3 and is subject to removal in a future release.
Fortunately, the JSON_MERGE_PRESERVE() function was added in MySQL 8.0.3 as a synonym for JSON_MERGE(), and it therefore does the same thing that JSON_MERGE() does/did.
Therefore, instead of using JSON_MERGE(), use JSON_MERGE_PRESERVE() instead.
Alternatively, you can use JSON_MERGE_PATCH(), which performs an RFC 7396 compliant merge of two or more JSON documents, without preserving members having duplicate keys.
In MySQL, we can use the SHOW CREATE TABLE statement to generate a CREATE TABLE script for existing tables. This enables us to recreate the table without having to manually type out the table’s definition.