从MySQL JSON数据类型中提取不带引号的值

I have started using the JSON data type in mysql 5.7. Is there a way to extract a value without the quotation marks? For instance when setting up a virtual index.

我已经开始在mysql 5.7中使用JSON数据类型。有没有办法提取没有引号的值？例如，在设置虚拟索引时。

Example:

例：

mysql> INSERT INTO test (data) VALUES ('{"type": "user" , 
"content" : { "username": "jdoe", "firstname" : "John", "lastname" : "Doe" } }');

mysql> SELECT json_extract(data,'$.type') FROM test;
+-----------------------------+
| json_extract(data,'$.type') |
+-----------------------------+
| "user"                      |
+-----------------------------+

How to get

如何获得

+-----------------------------+
| json_extract(data,'$.type') |
+-----------------------------+
| user                        |
+-----------------------------+

？

6 个解决方案

#1

You can use JSON_UNQUOTE() method:

您可以使用JSON_UNQUOTE（）方法：

SELECT JSON_UNQUOTE(json_extract(data,'$.type')) FROM test;

This method will deal with internal quotes, for instance:

此方法将处理内部引号，例如：

SET @t1 := '{"a": "Hello \\\"Name\\\""}';
SET @j := CAST(@t1 AS JSON);
SET @tOut := JSON_EXTRACT(@j, '$.a');
SELECT @t1, @j, @tOut, JSON_UNQUOTE(@tOut), TRIM(BOTH '"' FROM @tOut);

will give:

会给：

@t1     : {"a": "Hello \"Name\""}
@j      : {"a": "Hello \"Name\""}
@tOut   : "Hello \"Name\""
unquote : Hello "Name"
trim    : Hello \"Name\

I believe that the unquote is better in almost all circumstances.

我相信在几乎所有情况下，unquote都会更好。

#2

You can use ->> operator to extract unquoted data, simply!

您可以使用 - >>运算符来简单地提取不带引号的数据！

SELECT JSONCOL->>'$.PATH' FROM tableName

Two other ways:

另外两种方式：

JSON_UNQUOTE(JSON_EXTRACT(column, path))
JSON_UNQUOTE（JSON_EXTRACT（列，路径））
JSON_UNQUOTE(column->path)
JSON_UNQUOTE（柱 - >路径）

Note: Three different ways yield to the same command, as EXPLAIN explains:

注意：有三种不同的方法可以产生相同的命令，例如EXPLAIN解释：

As with ->, the ->> operator is always expanded in the output of EXPLAIN, as the following example demonstrates:

与 - >一样， - >>运算符总是在EXPLAIN的输出中展开，如下例所示：
EXPLAIN SELECT c->>'$.name' AS name FROM jemp WHERE g > 2 ;
SHOW WARNINGS ;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

阅读更多关于MySQL参考手册https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-inline-path

Note: The ->> operator was added in MySQL 5.7.13

注意：在MySQL 5.7.13中添加了 - >>运算符

#3

you can use CAST() function to convert from json object to varchar

您可以使用CAST（）函数将json对象转换为varchar

SELECT CAST(json_extract(data,'$.type') AS VARCHAR) FROM test;

#4

I have found a solution that is most clean. CAST function didn't work, and @Pryanshu's answer can be made independent from the value length by using

我找到了一个最干净的解决方案。 CAST功能不起作用，@ Pryanshu的答案可以通过使用独立于值长度

SELECT TRIM(BOTH '"' FROM json_extract(data,'$.type')) FROM test;

#5

-1

SELECT left(right(json_extract(data,'$.type'),5),4) FROM test;

#6

-1

You can also modify the column itself so that the quotes are not in the generated column

您还可以修改列本身，以便引号不在生成的列中

alter table your_table add your_field varchar(25) GENERATED ALWAYS AS (TRIM(BOTH '"' FROM json_extract(json_field,'$.your_field')))

#1