I have stored JSON associative array data in the column of the table in MySQL 5.5. I want to search in JSON at one specific array key with key and value my JSON structure :
x
{
"Interface_1":{
"Caption":"[00000004] Intel(R) Dual Band Wireless-AC 3160",
"MACAddress":"D0:7E:35:D3:C1:52",
"DHCPServer":"192.168.1.1",
"IPAddress":"192.168.1.101",
"DNSServerSearchOrder":[
"8.8.8.8",
"9.9.9.9"
],
"DefaultIPGateway":"192.168.1.1",
"IPSubnet":"255.255.255.0"
},
"Interface_2":{
"Caption":"[00000011] VMware Virtual Ethernet Adapter for VMnet1",
"MACAddress":"00:50:56:C0:00:01",
"IPAddress":[
"192.168.29.1",
"fe80::d9f4:2bfa:59f:b9ba"
],
"IPSubnet":[
"255.255.255.0",
"64"
]
},
"Interface_3":{
"Caption":"[00000013] VMware Virtual Ethernet Adapter for VMnet8",
"MACAddress":"00:50:56:C0:00:08",
"IPAddress":[
"192.168.71.1",
"fe80::acec:db96:af3a:4680"
],
"IPSubnet":[
"255.255.255.0",
"64"
]
}
}
So for search by Key Value, I have this query :
SELECT * FROM `extable`
WHERE network RLIKE '"MACAddress":"[[:<:]]00:50:56:C0:00:08[[:>:]]"'
It is working fine but my problem is that I can not select Interface_1 and search MACAddress on it.
I want to select Interface_1
and search MACAddress
on it.
Advertisement
Answer
You can include the Interface_1
object key into your regular expression, searching from that point but not past any }
(end of object) character for the MAC address:
SELECT * FROM `extable`
WHERE network RLIKE '"Interface_1":{[^}]*"MACAddress":"[[:<:]]00:50:56:C0:00:08[[:>:]]"'