如何在MySQL中使用JSON_EXTRACT并获取不带引号的字符串？

If I have a simple SELECT statement like this:

如果我有一个像这样的简单SELECT语句:

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

I would expect it to return Alexander , but instead it returns "Alexander". How can I get rid of the quotes? Why does this function even return the quotes too?

我希望它能归还亚历山大,但它会归还“亚历山大”。我该如何摆脱报价?为什么这个函数甚至也会返回引号?

3 个解决方案

#1

-2

you can use replace() with it to remove quotation marks

你可以使用replace()来删除引号

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

#2

You can use JSON_UNQUOTE to achieve this. select JSON_UNQUOTE(JSON_EXTRACT(base, '$.scope')) as scope from t_name

您可以使用JSON_UNQUOTE来实现此目的。选择JSON_UNQUOTE(JSON_EXTRACT(base,'$ .scope'))作为t_name的范围

ref:

#3

You can use SUBSTRING

您可以使用SUBSTRING

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

#1

-2