When working with JSON and MySQL, sometimes you might need to escape strings so that their quote characters don’t interfere with the interpretation of the JSON document. In such cases, you can use the JSON_QUOTE() function to escape potentially problematic strings.
JSON_QUOTE() is used to produce a valid JSON string literal that can be included in a JSON document. For example, you might want an element in an array to have the text null as a string literal, rather than having an actual null value. This function can be used to ensure that the text is added as a string literal, rather than a null value.
To use it, simply call the function while passing in the string.
Syntax
The syntax goes like this:
JSON_QUOTE(string)
Where string is the string to be escaped.
Example 1 – Basic Usage
Here’s an example to demonstrate.
First, let’s look at what happens when we use the JSON_ARRAY() function to create an array.
SELECT JSON_ARRAY("Hot", "Warm", "Cold") AS 'Result';
Result:
+-------------------------+ | Result | +-------------------------+ | ["Hot", "Warm", "Cold"] | +-------------------------+
The result is an array that contains 3 elements. We know it’s an array because it starts with an opening square bracket ([) and ends with a closing square bracket (]). We also know that each element is a string, because they’re surrounded by double quotes. A comma separates each element.
Now let’s see what happens when we use the JSON_QUOTE() instead.
SELECT JSON_QUOTE('"Hot", "Warm", "Cold"') AS 'Result';
Result:
+-------------------------------+ | Result | +-------------------------------+ | "\"Hot\", \"Warm\", \"Cold\"" | +-------------------------------+
We don’t get an array. We get a string. We know it’s a string because it starts and ends with a double quote character. Any double quotes within the string are escaped with the backslash character (\).
If these characters weren’t escaped, the first double quote (after Hot) would inadvertently terminate the string. Therefore, we wouldn’t be able to include the rest of the string. By escaping certain characters, we’re telling MySQL not to interpret those characters as it normally would.
Example 2 – Numbers
The same concept applies to numbers, except that numbers aren’t enclosed in double quotes.
So we can create an array that contains 3 elements, all of which are numbers.
SELECT JSON_ARRAY(1, 2, 3) AS 'Result';
Result:
+-----------+ | Result | +-----------+ | [1, 2, 3] | +-----------+
And here’s what happens if we switch over to the JSON_QUOTE() function.
SELECT JSON_QUOTE('[1, 2, 3]') AS 'Result';
Result:
+-------------+ | Result | +-------------+ | "[1, 2, 3]" | +-------------+
So we get the same result, except that the whole array is enclosed in double quotes. This makes it a string instead of an array.
Example 3 – Adding to Arrays/Objects
We could take this string literal and add it as an element in an array.
SELECT JSON_ARRAY(JSON_QUOTE('[1, 2, 3]'), 8, 9) AS 'Result';
Result:
+-------------------------+ | Result | +-------------------------+ | ["\"[1, 2, 3]\"", 8, 9] | +-------------------------+
In this case, the string literal is the first element, with 8 and 9 being the second and third elements respectively.
We could also use this string literal in an object.
SELECT JSON_OBJECT('Key', JSON_QUOTE('[1, 2, 3]')) AS 'Result';
Result:
+--------------------------+
| Result |
+--------------------------+
| {"Key": "\"[1, 2, 3]\""} |
+--------------------------+
Example 4 – Extracting Values
So if we need to extract values from our JSON document, it will be interpreted as a string literal instead of an array.
First, here’s what happens if we set an array with the three numbers as separate elements, then extract the first element from the array.
SET @data1 = JSON_ARRAY(1, 2, 3); SELECT @data1 AS '@data1', JSON_EXTRACT(@data1, '$[0]');
Result:
+-----------+------------------------------+ | @data1 | JSON_EXTRACT(@data1, '$[0]') | +-----------+------------------------------+ | [1, 2, 3] | 1 | +-----------+------------------------------+
So this array consists of three elements – each element is a separate element in the array.
Now, here’s what happens if we use JSON_QUOTE() to have all three numbers and their square brackets converted into a string literal, add that to the array, then extract the first element of the array.
SET @data2 = JSON_ARRAY(JSON_QUOTE('[1, 2, 3]'));
SELECT
@data2 AS '@data2',
JSON_EXTRACT(@data2, '$[0]');
Result:
+-------------------+------------------------------+ | @data2 | JSON_EXTRACT(@data2, '$[0]') | +-------------------+------------------------------+ | ["\"[1, 2, 3]\""] | "\"[1, 2, 3]\"" | +-------------------+------------------------------+
So in this case, the array consists of only one element – the whole string literal.