0

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?

1
  • Check the details of the version of MySQL you are using -- JSON support has been evolving. – Rick James Mar 19 '20 at 5:47
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.

2
  • Each JSON object would translate to multiple records in the DB, so I wouldn't be able to store the JSON object alongside the, say, unique ids without duplicating the JSON object. I'm reading up on JSON type in MySQL. Thanks. – DatabaseNewbie Mar 9 '20 at 11:51
  • You could also consider two separate tables, with a relationship. One table could contain your unique ids or other data you're interested in searching through, and just include a column which references the row id of a JSON record in some other table. This way you would not need to keep duplicate JSON object records. Good luck! – Crayons Mar 9 '20 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.