CODEWITHSUNDEEP
mysqlsqljson
lock
lock

Reputation: 739

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.

Upvotes: 0

Views: 1367

Answers (3)

Vanderson Andrade
Vanderson Andrade

Reputation: 362

I ended up using common_schema: https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/common-schema/common_schema-2.2.sql

Upvotes: 0

Nick
Nick

Reputation: 147166

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

Upvotes: 2

kartheek7895
kartheek7895

Reputation: 351

You can use MySql inbuilt json functions for parsing/searching.

SELECT * FROM `extable` WHERE JSON_CONTAINS(network, "00:50:56:C0:00:08", '$.*.MACAddress')

Edit: Just realized this is only supported in >=MySQL 5.7

Upvotes: 1

Related Questions