Извлечение значения без кавычки из типа данных MySQL JSON

Я начал использовать тип данных JSON в mysql 5.7. Есть ли способ извлечь значение без кавычек? Например, при настройке виртуального индекса.

Пример:

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

Как получить

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

Ответ 1

Я нашел решение, которое является самым чистым. Функция CAST не работает, и ответ @Pryanshu может быть сделан независимо от длины значения с помощью

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

Ответ 2

Вы можете использовать метод JSON_UNQUOTE():

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

Этот метод будет обрабатывать внутренние кавычки, например:

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);

даст:

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

Я считаю, что недокачка лучше почти во всех обстоятельствах.

Ответ 3

Вы можете использовать → > оператор для извлечения некотируемых данных просто!

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

Два других способа:

JSON_UNQUOTE (JSON_EXTRACT (столбец, путь))
JSON_UNQUOTE (столбцы > путь)

Примечание. Три разных способа дают ту же команду, что и EXPLAIN:

Как и в случае с → , оператор → > всегда выводится на выход 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

Примечание. Оператор → > был добавлен в MySQL 5.7.13

Ответ 4

вы можете использовать CAST() для преобразования из json объекта в varchar

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

Ответ 5

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

Ответ 6

Вы также можете изменить сам столбец, чтобы кавычки не были в созданном столбце

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