it’s a universal truth that one of the primary jobs of backend developers is drinking from the firehose of json the frontends are constantly spraying at us. normally, we pick apart all those key/value pairs and slot them into the neatly-arranged columns of our db, keeping our database structure nice and normal. but sometimes there are good reasons to just store a whole json object or array as an undifferentiated blob.
in the dark, old days, this json would go in a text column, which was fine if all we needed to do was store it and return it. but if we needed to do something more complex like, extract certain values from that json or, god forbid, use one in a WHERE clause, things could get out of hand pretty quickly.
fortunately, modern mysql supports JSON as a native data type and offers a whole host of useful json functions, allowing us to work with json data in a way that’s almost pleasurable. in this post we’re going to go over extracting values from json and using json data in WHERE clauses.
