How can I use JSON_EXTRACT in MySQL and get a string without quotes?

Question

If I have a simple SELECT statement:

SELECT JSON_EXTRACT('{"username":"Alexander"}', '$.username'); 

I expect him to return Alexander , but instead return "Alexander" . "Alexander" How can I get rid of quotes? Why does this function even return quotes?

Answer 1

you can use replace () with it to remove quotes

 SELECT replace(JSON_EXTRACT('{"username":"Alexander"}', '$.username'), '\"', ''); 

Answer 2

You can use JSON_UNQUOTE to achieve this.

 select JSON_UNQUOTE(JSON_EXTRACT(base, '$.scope')) as scope from t_name 

link:

Answer 3

You can use SUBSTRING

 SELECT SUBSTRING( JSON_EXTRACT ( '{"username":"Alexander"}', '$.username' ), 2, ( LENGTH( JSON_EXTRACT ( '{"username":"Alexander"}', '$.username' ) ) - 2 ) );