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

Your JSON path returns an array from your JSON document. It happens to hold only one value, but it is an array nonetheless. [Edit] your question to show us the document in question (the value of your `stored_json` column) and we may be able to help you change the path to extract the first array element. — O. Jones, Oct 27 '21 at 12:11

Bill Karwin · Accepted Answer · 2021-10-27T12:48:22.313

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

Great, thanks. For neatness I adjusted this a little to use stored_json->>'$.*.referal' instead of JSON_EXTRACT(stored_json, '$.*.referal') but essentially the same. — Jules, Oct 27 '21 at 13:18

MYSQL JSON search returns results in square brackets

1 Answers1

Linked