mysql - mysql 5.7 json 特性的一些问题

Question

表net_card用于存储json类型的网卡信息，这是我的表:

`net_card` (
  `serial_number` varchar(100) NOT NULL,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

net_card表的数据之一是:

序列号:12GLW02

data: {"netCard": [
{"ip": "172.16.14.67", "mac": "f8:bc:12:41:9e:a4", "bond": "no", "name": "em1", "serialNumber": "12GLW02"}, 
{"ip": "NULL", "mac": "f8:bc:12:41:9e:a5", "bond": "no", "name": "em2", "serialNumber": "12GLW02"}, 
{"ip": "NULL", "mac": "f8:bc:12:41:9e:a6", "bond": "no", "name": "em3", "serialNumber": "12GLW02"}, 
{"ip": "NULL", "mac": "f8:bc:12:41:9e:a7", "bond": "no", "name": "em4", "serialNumber": "12GLW02"}, 
{"ip": "127.0.0.1", "mac": "NULL", "bond": "no", "name": "lo", "serialNumber": "12GLW02"}]}

好吧，我想得到 ipList 并且它的值不是“NULL”，

我使用 sql select data->'$[0].netCard[*].ip' from net_card limit 1 得到包含“NULL”的结果；结果是:

["172.16.14.67", "NULL", "NULL", "NULL", "127.0.0.1"]

我想得到 ["172.16.14.67","127.0.0.1"]我该怎么办？

Answer 1

您可以为此使用字符串替换功能:

SELECT REPLACE(data->'$.netCard[*].ip', '"NULL", ', "") FROM net_card ;

但这绝对不是最好的解决方案。只有当 NULL 不在末尾时(因为 ,)，这个才有效。