How the JSON_VALID() function works in Mariadb?

The JSON_VALID() function is a simple but useful function for validating whether a JSON value is well-formed and valid. It takes a JSON value as an argument and returns 1 if it is valid, or 0 if it is not. The JSON_VALID() function can help you check the integrity and validity of a JSON document before performing other operations on it, such as parsing, extracting, or modifying.

Syntax

The syntax of the JSON_VALID() function is as follows:

JSON_VALID(json_val)

The parameter is:

json_val: The JSON value to be validated. It can be a column, a variable, or a literal.

Examples

In this section, we will show some examples of using the JSON_VALID() function with different JSON values and explain the output.

Example 1: Valid JSON values

Suppose we have some valid JSON values, such as:

SET @str = '"Hello"';
SET @num = '42';
SET @bool = 'true';
SET @null = 'null';
SET @obj = '{"name": "Jack", "age": 28, "married": false}';
SET @arr = '[1, 2, 3, 4, 5]';

We can use the JSON_VALID() function to validate them, as follows:

SELECT
  JSON_VALID(@str) AS str_valid,
  JSON_VALID(@num) AS num_valid,
  JSON_VALID(@bool) AS bool_valid,
  JSON_VALID(@null) AS null_valid,
  JSON_VALID(@obj) AS obj_valid,
  JSON_VALID(@arr) AS arr_valid;

The output is:

+-----------+-----------+------------+------------+-----------+-----------+
| str_valid | num_valid | bool_valid | null_valid | obj_valid | arr_valid |
+-----------+-----------+------------+------------+-----------+-----------+
|         1 |         1 |          1 |          1 |         1 |         1 |
+-----------+-----------+------------+------------+-----------+-----------+

The JSON_VALID() function returns 1 for all the valid JSON values.

Example 2: Invalid JSON values

Suppose we have some invalid JSON values, such as:

SET @bad_str = 'Hello';
SET @bad_num = '42.0.0';
SET @bad_bool = 'True';
SET @bad_null = 'NULL';
SET @bad_obj = '{"name": "Jack", "age": 28, "married": false';
SET @bad_arr = '[1, 2, 3, 4, 5,]';

We can use the JSON_VALID() function to validate them, as follows:

SELECT
  JSON_VALID(@bad_str) AS bad_str_valid,
  JSON_VALID(@bad_num) AS bad_num_valid,
  JSON_VALID(@bad_bool) AS bad_bool_valid,
  JSON_VALID(@bad_null) AS bad_null_valid,
  JSON_VALID(@bad_obj) AS bad_obj_valid,
  JSON_VALID(@bad_arr) AS bad_arr_valid;

The output is:

+---------------+---------------+----------------+----------------+---------------+---------------+
| bad_str_valid | bad_num_valid | bad_bool_valid | bad_null_valid | bad_obj_valid | bad_arr_valid |
+---------------+---------------+----------------+----------------+---------------+---------------+
|             0 |             0 |              0 |              0 |             0 |             0 |
+---------------+---------------+----------------+----------------+---------------+---------------+

The JSON_VALID() function returns 0 for all the invalid JSON values. The reasons for the invalidity are:

The string value must be enclosed in double quotes.
The number value must not have more than one decimal point.
The boolean value must be either true or false, in lowercase.
The null value must be null, in lowercase.
The object value must have a closing brace.
The array value must not have a trailing comma.

The JSON_VALID() function is not the only function that can work with JSON data. There are some other related functions that can be used for different purposes, such as:

JSON_TYPE(): This function returns the type of a JSON value, such as OBJECT, ARRAY, STRING, NUMBER, BOOLEAN, or NULL. It takes a JSON value and an optional path expression as arguments, and returns a string indicating the type. It can be used to inspect the structure and format of a JSON document, and perform different operations based on the type of the JSON value. For example:
```
SET @obj = '{"name": "Jack", "age": 28, "married": false}';
SET @arr = '[1, 2, 3, 4, 5]';

SELECT
  JSON_TYPE(@obj) AS obj_type,
  JSON_TYPE(@arr) AS arr_type;
```
The output is:
```
+----------+----------+
| obj_type | arr_type |
+----------+----------+
| OBJECT   | ARRAY    |
+----------+----------+
```
The JSON_TYPE() function returns the type of the JSON value as a string.
JSON_UNQUOTE(): This function removes the double quotes from a JSON value, if the value is a string. It takes a JSON value and an optional path expression as arguments, and returns a string without the double quotes. It can be used to extract the raw string value from a JSON document, and perform other operations on it, such as concatenation, comparison, or conversion.

Conclusion

The JSON_VALID() function is a simple function for validating whether a JSON value is well-formed and valid. It can help you check the integrity and validity of a JSON document before performing other operations on it, such as parsing, extracting, or modifying. The JSON_VALID() function is not the only function that can work with JSON data, as there are other related functions that can perform different tasks, such as quoting, extracting, searching, or modifying JSON values. By combining these functions, you can leverage the power of both JSON and SQL to handle complex and diverse data sources.

Syntax

Examples

Example 1: Valid JSON values

Example 2: Invalid JSON values

Related Functions

Conclusion