XF 2.1 Custom sql to find the most laughed at comments stored in reactions blob

[HJW]

Hello,

As the reactions are stored in a blob (like {"1":11,"2":3,"4":5}) does anyone have a clue how to order by the most laughed at comments ("3")?

thanks

 

[Kirby]

Whey do you want to mess with the JSON?

SELECT COUNT(*) AS total, content_type, content_id
FROM xf_reaction_content
WHERE reaction_id = 3
GROUP BY content_type, content_id
ORDER BY total DESC