Convert JSON to Strings in MySQL

In MySQL, you can convert JSON data to strings using the JSON_UNQUOTE() function, which removes JSON-specific quotes from a JSON field or value.

In this tutorial, you will learn how to use the JSON_UNQUOTE() function to convert JSON data to regular strings for easier processing and display in MySQL.

Converting JSON to Strings in MySQL

When working with JSON data in MySQL, it may be necessary to convert JSON values to standard strings for various operations, such as displaying data in reports or using it in concatenations. The JSON_UNQUOTE() function allows you to remove the extra quotes from JSON strings, effectively converting them to regular strings.

Examples: Converting JSON to Strings in MySQL

Here are some examples that demonstrate how to use the JSON_UNQUOTE() function in MySQL to convert JSON data to regular strings.

1. Converting a Simple JSON String to a Regular String

To convert a JSON string such as '"Hello World!"' into a regular string:

</>
Copy
SELECT JSON_UNQUOTE('"Hello World!"') AS unquoted_string;

This query removes the outer JSON quotes and returns the result Hello World! as a regular string.

Converting JSON to Strings in MySQL - Example: Converting a Simple JSON String to a Regular String

2. Extracting and Converting JSON Data in a Column

If you have a table employees with a JSON column details that contains a field "position", you can extract and convert this JSON field to a string:

</>
Copy
SELECT JSON_UNQUOTE(JSON_EXTRACT(details, '$.position')) AS position
FROM employees;

This query extracts the "position" field from the details column and removes the JSON-specific quotes, returning the position as a regular string.

3. Converting JSON Arrays to Strings

To convert a JSON array '["apple", "banana", "cherry"]' into a comma-separated string:

</>
Copy
SELECT JSON_UNQUOTE(REPLACE(JSON_EXTRACT('["apple", "banana", "cherry"]', '$'), '"', '')) AS fruits_string;

This query extracts the JSON array and removes the quotes, returning apple, banana, cherry as a regular comma-separated string.

Converting JSON to Strings in MySQL - Example: Converting JSON Arrays to Strings