SQLize | PHPize | SQLtest

create table tbl (id int, home json, status int); insert into tbl values ( 1, '{ "elasticity":[ { "retailer":"reatailer1", "totalsales":1136337.00, "elasticity":-0.82, "brand":"GW four Oz (Original)", "price":11 }, { "retailer":"reatailer2", "totalsales":1067989.00, "elasticity":-1.16, "brand":"GW four Oz 1 (Original)", "price":13 }, { "retailer":"reatailer3", "totalsales":360028.00, "elasticity":-0.45, "brand":"GW four Oz 2 (Original)", "price":12.86 }, { "retailer":"reatailer4", "totalsales":125735.00, "elasticity":-2.35, "brand":"GW four Oz 3 (Original)", "price":11 }, { "retailer":"reatailer5", "totalsales":2730123.00, "elasticity":-0.54, "brand":"GW four Oz 3 (Original)", "price":9.78 }, { "retailer":"reatailer9", "totalsales":4597069.00, "elasticity":-0.74, "brand":"GW four Oz 4 (Original)", "price":9.44 } ], "priceandvolumevariance":[ { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-3.1, "dollarimpact":9.3, "volumeimpact":-7.8 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-2.5, "dollarimpact":7.5, "volumeimpact":-6.3 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-1.9, "dollarimpact":5.7, "volumeimpact":-4.8 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-0.8, "dollarimpact":2.4, "volumeimpact":-2.0 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-0.4, "dollarimpact":1.2, "volumeimpact":-0.1 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":0.0, "dollarimpact":0.0, "volumeimpact":0.0 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":0.7, "dollarimpact":-2.1, "volumeimpact":1.8 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-3.1, "dollarimpact":9.3, "volumeimpact":-7.8 }, { "retailer":"reatailer4", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-2.5, "dollarimpact":7.5, "volumeimpact":-6.3 } ] }', 3 ), (2, '{ "elasticity":[ { "retailer":"reatailer1", "totalsales":1136337.00, "elasticity":-0.82, "brand":"GW four Oz 123 (Original)", "price":15.01 }, { "retailer":"reatailer2", "totalsales":1067989.00, "elasticity":-1.16, "brand":"GW four Oz 1 (Original)", "price":13.42 }, { "retailer":"reatailer3", "totalsales":360028.00, "elasticity":-0.45, "brand":"GW four Oz 2 (Original)", "price":11 }, { "retailer":"reatailer4", "totalsales":125735.00, "elasticity":-2.35, "brand":"GW four Oz 3 (Original)", "price":10.33 }, { "retailer":"reatailer5", "totalsales":2730123.00, "elasticity":-0.54, "brand":"GW four Oz 3 (Original)", "price":9.78 }, { "retailer":"reatailer6", "totalsales":4597069.00, "elasticity":-0.74, "brand":"GW four Oz 4 (Original)", "price":11 } ], "priceandvolumevariance":[ { "retailer":"reatailer6", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-3.1, "dollarimpact":9.3, "volumeimpact":-7.8 }, { "retailer":"reatailer6", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-2.5, "dollarimpact":7.5, "volumeimpact":-6.3 }, { "retailer":"reatailer6", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-1.9, "dollarimpact":5.7, "volumeimpact":-4.8 }, { "retailer":"reatailer6", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-0.8, "dollarimpact":2.4, "volumeimpact":-2.0 }, { "retailer":"reatailer6", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":-0.4, "dollarimpact":1.2, "volumeimpact":-0.1 }, { "retailer":"reatailer6", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":0.0, "dollarimpact":0.0, "volumeimpact":0.0 }, { "retailer":"reatailer6", "brand":"GW four Oz (Original)", "aisle":"Baby", "pricechange":0.7, "dollarimpact":-2.1, "volumeimpact":1.8 }, { "retailer":"reatailer6", "brand":"GW four Oz (Nighttime)", "aisle":"Baby", "pricechange":-3.1, "dollarimpact":9.3, "volumeimpact":-7.8 }, { "retailer":"reatailer6", "brand":"GW four Oz (Nighttime)", "aisle":"Baby", "pricechange":-2.5, "dollarimpact":7.5, "volumeimpact":-6.3 } ] }', 3), (4, '{"id":40, "value":0}', 2); select * from tbl where status = 3 and json_contains(home, '11', '$.elasticity[0].price')