Willem van Gerven Willem van Gerven - 6 months ago 41
SQL Question

Extract value without quotation mark from MySQL JSON data type

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


?

Answer

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