Extract value without quotation mark from MySQL JSON data type

You can use JSON_UNQUOTE() method:

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.

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(column->path)

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

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

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)

read more on MySQL Reference Manual 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