This is an interesting example of json over-use in sql. Even if the provided solution works well for the given amount of data it is harder to understand, harder to code and harder to maintain than a simple table would be. I would never use a json column to store such data. In this case I consider solutions with JSON_TABLE and the like viable only for extreme cases, e.g. when I’m working with data from an external system and I have no control over how it is stored. Otherwise, i’d stay away from json.
In my own development I’ve used json for very simple use cases like storing a simple array of tags attached to a row (to an order, invoice, etc.) that I’m almost 100% certain I will not need to search for. I once tried using a json column to store a multidimensional array representing structured data just to see what it’s like and while it works this is the part of my code I don’t like to come back to or modify because it is much harder to understand and debug than if I stored the data in separate tables.
Storing a json object in a column is fine. I was thinking like @Lemon_Juice once before but making some tests showed, that searching for values in a json object is as fast as searching for the value of a column, some times even faster (please don’t ask me why but we made tests with many different combinations and sizes up to million of rows)
Storing a json array in a field is nothing I would do also normally. But in this case it is ok because the data is only needed once (so really only one time I request it after it was written)
I think performance is not relevant unless the amount of data is huge. Still, I would use a separate table for the ease of use and not for performance. Additionally, database can keep referential integrity and data structure for free then why not take advantage of it? A json column will accept any garbage data without complaining.
But I must admit that keeping data in the same table sometimes is enticing because I as a developer can see it all in one view in a database admin tool when browsing a table and I don’t have to switch to another table and do lookups to see related data - this is a plus. I think for simple structures it might be okay. Myself am guilty of this and when a user could have several email addresses or phone numbers I stored them in a json array or even in a text field as comma-separated values - just because creating additional tables for such simple structures when there’s no requirement for searching seemed too much hassle. For me your case is on the edge of what I’d consider acceptable, everyone will feel different on that matter.
Anyway, this reminds me of Wordpress, whose developers push this idea to the extreme by storing complex objects in text fields as PHP serialized strings. Not only can’t you use SQL json functions to query and extract the data but even changing something by hand is very difficult and error prone if you don’t run the values through appropriate functions in PHP. That’s why I believe if you want to learn worst coding practices - look at Wordpress!