mysql - MySQL:json提取删除引号

我正在使用MySQL 5.7.12，这是我的查询。

select objectid, (JSON_EXTRACT(fullobject, '$.mail')) as mail from openidm.managedobjects where objecttypes_id=5 and (JSON_EXTRACT(fullobject, '$.mail'))='[email protected]';

这是输出

--------------------------------------------------
| objectid          | mail                       |
--------------------------------------------------
| 001f73d9-f3d      | "[email protected]"           |
--------------------------------------------------

我想从输出中删除引号，但是由于我使用的是5.7.12，所以我无法使用JSON_UNQUOTE()来实现。

我尝试了REPLACE()：

select objectid, REPLACE((JSON_EXTRACT(fullobject, '$.mail')), '\"', '') as mail from openidm.managedobjects where objecttypes_id=5 and (JSON_EXTRACT(fullobject, '$.mail'))='[email protected]';

但是输出是完全不相关的：

----------------------------------------------------------------------------
| objectid          | mail                                                 |
----------------------------------------------------------------------------
| 001f73d9-f3d      | 6C75632E6170617369383840676D61696C2E636F6D           |
----------------------------------------------------------------------------

编辑

这是我的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

这是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=="}}}}

最佳答案

您可以像这样使用trim()函数：

select objectid,
       TRIM(BOTH '"' FROM
                 (JSON_EXTRACT(fullobject, '$'))
       ) as mail
from openidm.managedobjects
where objecttypes_id=5
  and (JSON_EXTRACT(fullobject, '$.mail'))='[email protected]';

编辑使用$而不是$.mail，请选择Fiddle