How the JSON_UNQUOTE() function works in Mariadb?

The JSON_UNQUOTE() function is a simple but useful function for removing the double quotes from a JSON value. It takes a JSON value as an argument and returns a string without the double quotes, if the value is a string. If the value is not a string, the function returns the value as it is. The JSON_UNQUOTE() function can help you extract the raw string value from a JSON document, and perform other operations on it, such as concatenation, comparison, or conversion.

Syntax

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

JSON_UNQUOTE(json_val)

The parameter is:

json_val: The JSON value whose double quotes to be removed. It can be a column, a variable, or a literal.

Examples

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

Example 1: String value

Suppose we have a JSON value that is a string, such as:

SET @str = '"Hello"';

We can use the JSON_UNQUOTE() function to remove the double quotes from the string, as follows:

SELECT JSON_UNQUOTE(@str) AS unquoted_str;

The output is:

+--------------+
| unquoted_str |
+--------------+
| Hello        |
+--------------+

The JSON_UNQUOTE() function returns the string value without the double quotes.

Example 2: Non-string value

Suppose we have a JSON value that is not a string, such as:

SET @num = '42';
SET @bool = 'true';
SET @null = 'null';

We can use the JSON_UNQUOTE() function to return the value as it is, as follows:

SELECT
  JSON_UNQUOTE(@num) AS unquoted_num,
  JSON_UNQUOTE(@bool) AS unquoted_bool,
  JSON_UNQUOTE(@null) AS unquoted_null;

The output is:

+--------------+---------------+---------------+
| unquoted_num | unquoted_bool | unquoted_null |
+--------------+---------------+---------------+
| 42           | true          | null          |
+--------------+---------------+---------------+

The JSON_UNQUOTE() function does not remove the double quotes from non-string values, as they do not have any.

Example 3: Object or array

Suppose we have a JSON value that is an object or an array, such as:

SET @obj = '{"name": "Jack", "age": 28, "married": false}';
SET @arr = '[1, 2, 3, 4, 5]';

We can use the JSON_UNQUOTE() function to return the value as it is, as follows:

SELECT
  JSON_UNQUOTE(@obj) AS unquoted_obj,
  JSON_UNQUOTE(@arr) AS unquoted_arr;

The output is:

+-----------------------------------------------+-----------------+
| unquoted_obj                                  | unquoted_arr    |
+-----------------------------------------------+-----------------+
| {"name": "Jack", "age": 28, "married": false} | [1, 2, 3, 4, 5] |
+-----------------------------------------------+-----------------+

The JSON_UNQUOTE() function does not remove the double quotes from object or array values, as they are not strings.

The JSON_UNQUOTE() 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_QUOTE(): This function is the inverse of the JSON_UNQUOTE() function. It takes a string as an argument and returns a JSON value with double quotes around it. It can be used to convert a string into a valid JSON value, and escape any special characters in it. For example:
```
SET @str = 'Hello';

SELECT
  JSON_QUOTE(@str) AS quoted_str;
```
The output is:
```
+-------------+
| quoted_str  |
+-------------+
| "Hello"     |
+-------------+
```
The JSON_QUOTE() function returns the string value with double quotes around it.
JSON_REPLACE(): This function replaces the value of a JSON document at a given path with a new value. It takes a JSON document, one or more path-value pairs as arguments, and returns a modified JSON document. If the path does not exist, the function does nothing. If the path is the root ‘$’, the function replaces the whole document. For example:
```
SET @obj = '{"name": "Jack", "age": 28, "married": false}';

SELECT JSON_REPLACE(@obj, '$.age', 29, '$.married', true) AS replaced_obj;
```
The output is:
```
+----------------------------------------------+
| replaced_obj                                 |
+----------------------------------------------+
| {"name": "Jack", "age": 29, "married": true} |
+----------------------------------------------+
```
The JSON_REPLACE() function returns the modified JSON object with the new values for the age and married properties.

Conclusion

The JSON_UNQUOTE() function is a simple function for removing the double quotes from a JSON value. It can help you extract the raw string value from a JSON document, and perform other operations on it, such as concatenation, comparison, or conversion. The JSON_UNQUOTE() 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: String value

Example 2: Non-string value

Example 3: Object or array

Related Functions

Conclusion