How the JSON_TYPE() function works in Mariadb?

The JSON_TYPE() function is a simple but useful function for inspecting the type of a JSON value. It takes a JSON value as an argument and returns a string indicating its type, such as OBJECT, ARRAY, STRING, NUMBER, BOOLEAN, or NULL. The JSON_TYPE() function can help you understand the structure and format of a JSON document, and perform different operations based on the type of the JSON value.

Syntax

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

JSON_TYPE(json_val)

The parameter is:

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

Examples

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

Example 1: Simple values

Suppose we have some simple JSON values, such as:

SET @str = '"Hello"';
SET @num = '42';
SET @bool = 'true';
SET @null = 'null';

We can use the JSON_TYPE() function to get their types, as follows:

SELECT
  JSON_TYPE(@str) AS str_type,
  JSON_TYPE(@num) AS num_type,
  JSON_TYPE(@bool) AS bool_type,
  JSON_TYPE(@null) AS null_type;

The output is:

+----------+----------+-----------+-----------+
| str_type | num_type | bool_type | null_type |
+----------+----------+-----------+-----------+
| STRING   | INTEGER  | BOOLEAN   | NULL      |
+----------+----------+-----------+-----------+

The JSON_TYPE() function returns the type of each JSON value as a string. The possible types for simple values are STRING, NUMBER, BOOLEAN, or NULL.

Example 2: Object

Suppose we have a JSON object, such as:

SET @obj = '{"name": "Jack", "age": 28, "married": false}';

We can use the JSON_TYPE() function to get the type of the object, as well as the type of each property, as follows:

SELECT JSON_TYPE(@obj) AS obj_type;

The output is:

+----------+
| obj_type |
+----------+
| OBJECT   |
+----------+

The JSON_TYPE() function returns the type of the object as OBJECT.

Example 3: Array

Suppose we have a JSON array, such as:

SET @arr = '[1, 2, 3, 4, 5]';

We can use the JSON_TYPE() function to get the type of the array, as well as the type of each element, as follows:

SELECT JSON_TYPE(@arr) AS arr_type;

The output is:

+----------+
| arr_type |
+----------+
| ARRAY    |
+----------+

The JSON_TYPE() function returns the type of the array as ARRAY.

The JSON_TYPE() 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_VALID(): This function validates 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. It can be used to check the integrity and validity of a JSON document before performing other operations on it. For example:
```
SET @obj = '{"name": "Jack", "age": 28, "married": false}';
SET @bad = '{"name": "Jack", "age": 28, "married": false';

SELECT
  JSON_VALID(@obj) AS obj_valid,
  JSON_VALID(@bad) AS bad_valid;
```
The output is:
```
+----------+----------+
| obj_valid| bad_valid|
+----------+----------+
|        1 |        0 |
+----------+----------+
```
The JSON_VALID() function returns 1 for the valid JSON object, and 0 for the invalid JSON value that is missing a closing brace.
JSON_LENGTH(): This function returns the length of a JSON value, which is the number of elements in an array, the number of properties in an object, or 1 for other types of values. It takes a JSON value and an optional path expression as arguments, and returns an integer indicating the length. If the path expression does not match any value, the function returns null. For example:
```
SET @obj = '{"name": "Jack", "age": 28, "married": false}';
SET @arr = '[1, 2, 3, 4, 5]';

SELECT
  JSON_LENGTH(@obj) AS obj_length,
  JSON_LENGTH(@arr) AS arr_length,
  JSON_LENGTH(@arr, '$[0]') AS first_length;
```
The output is:
```
+-----------+-----------+-------------+
| obj_length| arr_length| first_length|
+-----------+-----------+-------------+
|         3 |         5 |           1 |
+-----------+-----------+-------------+
```
The JSON_LENGTH() function returns the number of properties in the object, the number of elements in the array, and 1 for the first element of the array.

Conclusion

The JSON_TYPE() function is a simple function for getting the type of a JSON value. It can help you understand the structure and format of a JSON document, and perform different operations based on the type of the JSON value. The JSON_TYPE() 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 validating, 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: Simple values

Example 2: Object

Example 3: Array

Related Functions

Conclusion