How to search JSON data associative array mysql 5.5

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 :

   {
   "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.

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[[:>:]]"'

Demo on dbfiddle

Advertisement

Answer