Skip to content

Instantly share code, notes, and snippets.

@jonathanvx
Last active November 26, 2020 17:12
Show Gist options
  • Save jonathanvx/513066eea8cb5919b648b2453db47890 to your computer and use it in GitHub Desktop.
Save jonathanvx/513066eea8cb5919b648b2453db47890 to your computer and use it in GitHub Desktop.
Using JSON with MySQL Stored Procedures
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}');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment