MySQL:json提取删除引号

Question

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

select objectid, (JSON_EXTRACT(fullobject, '$.mail')) as mail from openidm.managedobjects where objecttypes_id=5 and (JSON_EXTRACT(fullobject, '$.mail'))='fake@email.com';

这是输出

--------------------------------------------------
| objectid          | mail                       |
--------------------------------------------------
| 001f73d9-f3d      | "fake@email.com"           |
--------------------------------------------------

我想从输出中删除引号，但由于我使用的是 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'))='fake@email.com';

但输出完全不相关:

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

编辑

这是我的show create table openidm.managementobjects;

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":"fake@email.com","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=="}}}}

Answer 1

您可以使用trim()功能如下:

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

编辑使用$而不是$.mail，检查这个Fiddle