How to Save JSON Data in MySQL?

Question

I receive a series of JSONAPI (https://jsonapi.org/format/) documents as a response to an API call that I make in my application. I need to save the API responses to my database so I can reference it again in the future (I cannot make another API call that can guarantee that I get the same data again). In the JSON documents, there will be a series of unique ids that I can use as part of a LIKE query.

Is it a good idea to save a JSON string in a column of a table? One benefit of it is that I can save the entire JSON in a single row/column. I'm concerned about scaling -- and how LIKE once I start having a lot of rows/columns of JSON documents that I need to search.

Another option is to loop through each element in the JSONAPI's data element and save all of the data in their own columns and relationship tables. This feels like a more structured way to save the data, but I'm concerned it's overkill since I really only need to reference the JSON document on occasion and can use a LIKE.

Is there a best practice for this?

Answer 1

I don't think there's anything wrong with storing JSON objects in a DB record. Though I would use the actual JSON type, rather than just varchar or something of that sort. Using a JSON field type allows you to use MySQL functions like JSON_CONTAINS to search that object.

Depending on what, and how frequently you need to search this data, I would consider just pulling out the particular data you need to search through, and store that as it's own column alongside the whole JSON object. This way, you can easily search for what you need, and still keep the entire object in the event you need to reference it in the future.