MYSQL JSON search returns results in square brackets-CodePudding

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"
    }
}

CodePudding user response：

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(..., '["\\[\\]]', '')