Steve Wong
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?
HostName, IPAddresses
Row 1 : Machine1, {"ipaddress1":"10.1.112.34
Row 2 : Machine2, {"ipaddress1":"10.1.113.37
Row 3 : Machine3, {"ipaddress1":"10.1.112.37
Row 4 : Machine4, {"ipaddress1":"10.1.112.38
Row 5 : Machine5, {"ipaddress1":"10.1.112.40
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
How can I do the same as above when I am using Json structure at MySQL?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
*/
ASKER
Thanks for helping is usful
Open in new window
try that...