Flipping keys/values in SQL JSON

Have you ever come across SQL JSON that was injected in the improper order? Something along the lines of: {"0": "numDogs", "3": "numCats"} when you wanted {"numDogs": "0", "numCats": "3"}? Not only is it hard to make sense of your data (How would you sum up all the total numDogs across all the rows?) it is critically dangerous for the collision of keys. If you have a couple of rows you could manually UPDATE/replace the individual fields but this is still tricky if you have more then a couple rows.

I was throwing around this problem in my head and, utilizing some temp tables and the JSON cmds introduced in 5.7.8, came up with a series of SQL queries that could be a solution. They can help make the JSON's key:value => value:key (or in other words your new key:value). Note: this only works if your values are scalar (not array/object/etc because those arent valid keys).

#If you want to run multiple times you have to uncomment these to drop tables. Otherwise the tables are dropped when session ends
#DROP TEMPORARY TABLE tab;
#DROP TEMPORARY TABLE tmp1;

#Create a table to separate the keys and values
#REPLACE "mytable" and "json_data" and "id" with your table/column/primary key names
CREATE TEMPORARY TABLE tab
SELECT id, mykeys, myvalues
FROM (
SELECT *,
CASE WHEN JSON_VALID(json_data) THEN JSON_KEYS(json_data) ELSE null END AS mykeys,
CASE WHEN JSON_VALID(json_data) THEN JSON_EXTRACT(JSON_UNQUOTE(json_data), "$.*") ELSE null END AS myvalues
FROM mytable
WHERE json_data!="" and json_data!="null" and json_data is not null
) mytable;

#Create and fill a table that we are just going to use as index values
#Might be a clever way to add a sequence of values to a table but this is the way I did it...
#Just verify you insert more then the number of keys in the json you are "fixing". IE my example had 2 for numDogs and numCats
create temporary table tmp1 (id tinyint unsigned not null primary key);  
insert into tmp1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30);  

#REPLACE "mytable" and "id" with your table/primary key designations
UPDATE mytable jTable,
  (
select myresultstable.id, JSON_OBJECTAGG(col2,col1) as JSONresult from
(
select
tab.id,
JSON_UNQUOTE(json_extract(tab.mykeys, concat('$[', tmp1.id, ']'))) as col1,
JSON_UNQUOTE(json_extract(tab.myvalues, concat('$[', tmp1.id, ']'))) as col2
FROM tmp1
join tab
) myresultstable
WHERE myresultstable.col1 is not null and myresultstable.col2 is not null
group by myresultstable.id
  ) t2
SET jTable.json_data=JSONresult
WHERE t2.id = jTable.id;

I only ask you test and verify with your own environment before running these test queries because I am not a DBA (clearly) and this will overwrite your data 🖖