'MySQL: json extract remove quotes
I'm using MySQL 5.7.12 and this is my query.
select objectid, (JSON_EXTRACT(fullobject, '$.mail')) as mail from openidm.managedobjects where objecttypes_id=5 and (JSON_EXTRACT(fullobject, '$.mail'))='[email protected]';
And this is the output
--------------------------------------------------
| objectid | mail |
--------------------------------------------------
| 001f73d9-f3d | "[email protected]" |
--------------------------------------------------
I would like to remove the quotes from the output but since I'm using 5.7.12 I can't do it with JSON_UNQUOTE().
I tryed with REPLACE():
select objectid, REPLACE((JSON_EXTRACT(fullobject, '$.mail')), '\"', '') as mail from openidm.managedobjects where objecttypes_id=5 and (JSON_EXTRACT(fullobject, '$.mail'))='[email protected]';
but the output is totally unrelated:
----------------------------------------------------------------------------
| objectid | mail |
----------------------------------------------------------------------------
| 001f73d9-f3d | 6C75632E6170617369383840676D61696C2E636F6D |
----------------------------------------------------------------------------
EDIT
This is my show create table openidm.managedobjects;
CREATE TABLE `managedobjects` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`objecttypes_id` bigint(20) unsigned NOT NULL,
`objectid` varchar(255) COLLATE utf8_bin NOT NULL,
`rev` varchar(38) COLLATE utf8_bin NOT NULL,
`fullobject` mediumtext COLLATE utf8_bin,
PRIMARY KEY (`id`),
UNIQUE KEY `idx-managedobjects_object` (`objecttypes_id`,`objectid`),
KEY `fk_managedobjects_objectypes` (`objecttypes_id`),
CONSTRAINT `fk_managedobjects_objectypes` FOREIGN KEY (`objecttypes_id`) REFERENCES `objecttypes` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=445311 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
This is the fullobject json:
{"telephoneNumber":"077777777","mail":"[email protected]","msisdnVerified":"true","countryCode":"+44","opCoProfileId":"MSISDN","opCo":"3UK","userName":"077777777","partyID":"777777777","accountStatus":"active","creationDate":"Wed Jan 24 2018 11:33:02 GMT+0100 (CET)","_id":"001f73d9-f3d","_rev":"3","global_id":"001f73d9-f3d","CRSMID":"50000048","migrated":"false","mailVerified":"true","mailVerifiedDate":"Wed Jan 24 2018 11:33:03 GMT+0100 (CET)","dateOfBirth":"1988-02-02","password":{"$crypto":{"type":"x-simple-encryption","value":{"cipher":"AES/CBC/PKCS5Padding","salt":"1P3TMv71pASuLkyVLFNnEA==","data":"iyj/h0fMU6aiTTBLL/Bb2w==","iv":"gQ6a5cAJYrtek5vcX8+mfw==","key":"openidm-sym-default","mac":"ntv0yTBDzCb0n8kvW4UklQ=="}}}}
Solution 1:[1]
I guess that the error is related to the connector driver. I'm using mysql-connector-java-5.1.45 with SQL Developer. Still haven't found a resolution (tryed also another version) but doing the replace directly on the mysql server it works.
Solution 2:[2]
You can use trim() function like this:
select objectid,
TRIM(BOTH '"' FROM
(JSON_EXTRACT(fullobject, '$'))
) as mail
from openidm.managedobjects
where objecttypes_id=5
and (JSON_EXTRACT(fullobject, '$.mail'))='[email protected]';
EDIT use $ not $.mail, check this Fiddle
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | LucaP |
| Solution 2 |