MySQL中如何将JSON数组格式的contact_email字段转为字符串？

Hey there! Let's sort out that issue where you're trying to pull the plain email string from the JSON array stored in your contact_email field.

First, let's break down why you might be seeing errors or unexpected results with JSON_EXTRACT: that function returns a JSON-formatted value (so it'll still have quotes around it, like "ass@sss.ib"), and since your data is an array, you need to target the first element explicitly. Here are a few solid solutions depending on your MySQL version:

Solution 1: Use JSON_EXTRACT + JSON_UNQUOTE (Works for MySQL 5.7+) #

This is the explicit way to get the job done. First, extract the first element of the JSON array with JSON_EXTRACT, then strip the surrounding quotes with JSON_UNQUOTE:

SELECT JSON_UNQUOTE(JSON_EXTRACT(contact_email, '$[0]')) AS plain_email
FROM your_table_name;

• '$[0]' targets the first (and only, in your case) element of the JSON array
• JSON_UNQUOTE removes the double quotes around the extracted value to give you ass@sss.ib

Solution 2: Use the Shorthand Arrow Operator (MySQL 5.7.13+) #

If you're on a newer MySQL version, this shorthand combines JSON_EXTRACT and JSON_UNQUOTE into one step, which is cleaner:

SELECT contact_email->>'$[0]' AS plain_email
FROM your_table_name;

The ->> operator is just a shortcut for JSON_UNQUOTE(JSON_EXTRACT(...)), so it'll give you the exact same plain email string.

Solution 3: String Manipulation (For Older MySQL Versions <5.7) #

If you're stuck on a version that doesn't support JSON functions, you can use string trimming to strip off the array brackets and quotes:

SELECT TRIM(BOTH '"[]' FROM contact_email) AS plain_email
FROM your_table_name;

This works because your data is a single-element array—TRIM will remove all occurrences of [, ], and " from the start and end of the string, leaving you with the plain email.

Testing in phpMyAdmin #

Just pop any of these queries into the SQL tab of phpMyAdmin, replace your_table_name with the actual name of your table, and run it. You should see the plain_email column with the value ass@sss.ib exactly as you need it.

内容的提问来源于stack exchange，提问作者Shreyas Achar