Using JSON with MySQL Stored Procedures

json.sql

delimiter $$
drop procedure if exists county_summary$$
create procedure county_summary(query JSON)
BEGIN
DECLARE i_county varchar(255) default null;
DECLARE i_year int default null;
DECLARE i_month int default null;

set i_county = JSON_UNQUOTE(JSON_EXTRACT(query,'$.county'));
set i_year = JSON_EXTRACT(query,'$.year');
set i_month = JSON_EXTRACT(query,'$.month');

 if (i_county is not null) && (i_year is not null) && (i_month is not null) then
   select county, year,month, sum(total_price) as total_price, sum(houses_sold) as houses_sold 
   from summary 
   where county = i_county and year = i_year and month=i_month 
   group by county; 
 elseif (i_county is not null) && (i_year is not null) then
   select county, year, sum(total_price) as total_price, sum(houses_sold) as houses_sold 
   from summary 
   where year = i_year 
   and county = i_county
   group by county, year; 
 elseif (i_year is not null) && (i_month is not null) then
   select county, year, month, sum(total_price) as total_price, sum(houses_sold) as houses_sold 
   from summary 
   where year = i_year and month = i_month
   group by county; 
 elseif (i_year is not null) then
   select county, year, sum(total_price) as total_price, sum(houses_sold) as houses_sold 
   from summary 
   where year = i_year 
   group by county; 
 else
   select null as 'no input';
 end if; 


END $$
delimiter ;

-- Testing
call county_summary('{"year": 2010}');
call county_summary('{"county":"YORK","year": 2010,"month":12}');
call county_summary('{"county":"YORK","year": 2010}');
call county_summary('{"year": 2010,"month":12}');