Extract value without quotation mark from MySQL JSON data type
MysqlJsonMysql Problem Overview
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.
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 |
+-----------------------------+
?
Mysql Solutions
Solution 1 - Mysql
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
Solution 2 - Mysql
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.
Solution 3 - Mysql
A different method is;
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.type')) FROM test
Solution 4 - Mysql
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 EMPTY > on_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
Solution 5 - Mysql
you can use CAST() function to convert from json object to varchar
SELECT CAST(json_extract(data,'$.type') AS VARCHAR) FROM test;
Solution 6 - Mysql
SELECT left(right(json_extract(data,'$.type'),5),4) FROM test;
Solution 7 - Mysql
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')))
Solution 8 - Mysql
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
SELECT TRIM(BOTH '"' FROM json_extract(data,'$.type')) FROM test;