Extract value without quotation mark from MySQL JSON data type
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: NoteCode: 1003Message: /* select#1 */ selectjson_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
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.
MySQL 8.0.21 supports JSON_VALUE
function
Extracts a value from a JSON document at the path given in the specified document, and returns the extracted value, optionally converting it to a desired type. The complete syntax is shown here:
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])on_empty: {NULL | ERROR | DEFAULT value} ON EMPTYon_error: {NULL | ERROR | DEFAULT value} ON ERROR
If not specified by a RETURNING clause, the JSON_VALUE() function's return type is VARCHAR(512)
SELECT json_value(data,'$.type')FROM test;-- user