asked on

MySQL Json Query in a range,

I have a table called XYZ which has 2 fields one is storing Json Structure and the other is varchar, Those fields look like below.
HostName, IPAddresses
Row 1 : Machine1, {"ipaddress1":"10.1.112.34", "ipaddress2":"192.168.3.17"}
Row 2 : Machine2, {"ipaddress1":"10.1.113.37", "ipaddress2":"192.168.1.17"}
Row 3 : Machine3, {"ipaddress1":"10.1.112.37", "ipaddress2":"192.168.0.17"}
Row 4 : Machine4, {"ipaddress1":"10.1.112.38", "ipaddress2":"192.168.6.17"}
Row 5 : Machine5, {"ipaddress1":"10.1.112.40", "ipaddress2":"192.168.2.19"}
and I have finished to extract the detail of ip address by using select Hostname , IPAddresses -> '$.ipaddress1' as PrimIP from XYZ

My Question is how can I use "in" this keyword or something similar for those Json structure as I was using it in a normal SQL structure.
ie. let say I have a range of IP address of the ipaddress1. ('10.1.113.37', '10.1.112.38','10.1.112.40' )
if the IP range is as varchar I can simply using

select hostname , IPaddresses from XYZ where IPAddresses in ('10.1.113.37', '10.1.112.38','10.1.112.40' ). roll consisted with these ip address will be return.

How can I do the same as above when I am using Json structure at MySQL?

JesterToo

select hostname, IPaddresses from XYZ where IPaddresses like %10.1.113.37%
                                         or IPaddresses like %10.1.112.38%
                                         or IPaddresses like %10.1.112.40%;

Open in new window

try that...

ASKER CERTIFIED SOLUTION

JesterToo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

check this

drop table if exists test;
create table test(HostName varchar(100), IPAddress json);
insert into test values('Machine1', '{"ipaddress1":"10.1.112.34", "ipaddress2":"192.168.3.17"}');
insert into test values('Machine2', '{"ipaddress1":"10.1.113.37", "ipaddress2":"192.168.1.17"}');
insert into test values('Machine3', '{"ipaddress1":"10.1.112.37", "ipaddress2":"192.168.0.17"}');
insert into test values('Machine4', '{"ipaddress1":"10.1.112.38", "ipaddress2":"192.168.6.17"}');
insert into test values('Machine5', '{"ipaddress1":"10.1.112.40", "ipaddress2":"192.168.2.19"}');


select *
  from (select t1.*,JSON_EXTRACT(IPAddress, "$.ipaddress1") ipaddress1, 
               JSON_EXTRACT(IPAddress, "$.ipaddress2") ipaddress2 from test t1) t1
 where ipaddress1 in ('"10.1.113.37"', '"10.1.112.38"','"10.1.112.40"') 
    or ipaddress1 in ('"10.1.113.37"', '"10.1.112.38"','"10.1.112.40"')
/*
  	HostName	IPAddress	ipaddress1	ipaddress2
1	Machine2	{"ipaddress1": "10.1.113.37", "ipaddress2": "192.168.1.17"}	"10.1.113.37"	"192.168.1.17"
2	Machine4	{"ipaddress1": "10.1.112.38", "ipaddress2": "192.168.6.17"}	"10.1.112.38"	"192.168.6.17"
3	Machine5	{"ipaddress1": "10.1.112.40", "ipaddress2": "192.168.2.19"}	"10.1.112.40"	"192.168.2.19"

*/

Open in new window

Steve Wong

ASKER

Thanks for helping is usful