MYSQL JSON search returns results in square brackets

Question

I am conducting a simple search of JSON held in a MySQL database, however the results are returning enclosed in square brackets and quotes.

SELECT stored_json->>'$.*.referal' AS referal FROM table WHERE id =  100

results in

+------------+
| referal    |
+------------+
| ["search"] |
+------------+

Is there a way to get MYSQL to return the results without the brackets and quotes?

+------------+
| referal    |
+------------+
| search     |
+------------+

Thanks

Edit

JSON example

{
    "100": {
            "referal": "search"
    }
}

Answer 1

If you just want a single value from the array, then extract that value and unquote it:

SELECT JSON_UNQUOTE(JSON_EXTRACT(
    JSON_EXTRACT(stored_json, '$.*.referal'), '$[0]')) AS referal
FROM ...

+---------+
| referal |
+---------+
| search  |
+---------+

The JSON_UNQUOTE() function converts the result to a string, but if you give it a JSON array or object, it can't remove the brackets and double-quotes. Those just become part of the string. You must extract a single scalar value from the JSON, and then you can use JSON_UNQUOTE() to remove the double-quotes.

I suppose you might want to get results if there are more than one value in your JSON document.

+----------------------------+
| referal                    |
+----------------------------+
| ["search","word of mouth"] |
+----------------------------+

Sorry, I don't see a solution for this, other than:

REPLACE(..., '[', '')

And so on, removing the other characters you don't want.

In MySQL 8.0, the function REGEXP_REPLACE() can strip multiple characters in one pass:

REGEXP_REPLACE(..., '["\\[\\]]', '')