如何检索JSON数据关联数组MySQL5.5

Question

我在MySQL 5.5中将JSON关联数组数据存储在表的列中。我想在JSON中用key和value在JSON结构中搜索一个特定的数组键：

   {
   "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"
      ]
   }
}

因此，对于按键值搜索，我有这样的查询：

SELECT * FROM `extable` 
WHERE network RLIKE '"MACAddress":"[[:<:]]00:50:56:C0:00:08[[:>:]]"'

它工作得很好，但我的问题是我不能选择Interface_1并在上面搜索MACAddress。我想选择Interface_1并在上面搜索MACAddress。

Answer 1

您可以在正则表达式中包含Interface_1对象键，从该点开始搜索，但不超过任何} (对象结束)字符：

SELECT * FROM `extable` 
WHERE network RLIKE '"Interface_1":{[^}]*"MACAddress":"[[:<:]]00:50:56:C0:00:08[[:>:]]"'

Demo on dbfiddle

Answer 2

您可以使用MySql内置的json函数进行解析/搜索。

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

编辑:我刚刚意识到这只在>=MySQL 5.7中支持