JSON and MySQL Stored Procedures

You probably heard that MySQL 5.7 supports JSON.
But did you know that you can also use JSON with MySQL Stored Procedures - making them very flexible?

Less talk, more code:
(Data was used from the UK Land Registry that I worked on)

	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}');

view raw json.sql hosted with ❤ by GitHub