CODEWITHSUNDEEP
mysqljsondatabase
Onyx
Onyx

Reputation: 5762

How can I store JSON in a MySQL database table field?

I currently have an application that uses a third party API whose endpoints return JSON. When a component on the front-end is mounted, I execute a function which makes a GET request to my own back-end and in return my back-end makes a GET request to the third party API, then the response from the API is returned as JSON to the front-end.

I have a limited amount of allowed requests to that API so I want to make sure to save the response to my database so that when a future requests are made, my back-end would return what's in my database instead of making a whole new GET request.

I'm not sure if storing JSON is wise or possible and this is why I decided to ask. Under what data type should the JSON be saved and would there be any drawbacks to what I'm doing?

Upvotes: 3

Views: 6703

Answers (4)

igoras1993
igoras1993

Reputation: 705

Yes, this is possible. MySQL implements JSON datatype since 5.7 version.

If You are asking about technical details about how to operate with this datatype, here is excellent shortcut.

Just quoting few examples:

CREATING:

mysql> CREATE TABLE facts (sentence JSON);

INSERTING:

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

READING:

mysql> SELECT sentence->"$.mascot" FROM facts;

But I bet that a real question is about how wise it is to store a JSON in database.

So the general answer is:

if developers of particular RDBMS included such aproach in their implementation, it is intended and desired for use.

So, as long as it is good idea to format your data as a JSON at all, it should be also a good idea to store this data in JSON column in RDBMS. I do not have an experience in that particular implementation (prefer Postgresql rather than MySQL), but I had started using JSON datatype as soon as I've needed it and still I do not consider it as a bad decision or something.

Epecially, when you consider storing JSON formated data inside a file and hooking just paths inside database, using a JSON type instead should be a good idea. Almost always storing JSON formated data in files WILL be slower than inserting and querying JSON, Especially, when You need access only to a particular key-value pairs (you can query just a particular keys in ordinary selects).

HOWEVER, when Your data is not inteded to be stored as a JSON format AT ALL, it will be a bad idea to use a JSON datatype also. What kind of data does JSON not like? Basically, all sorts of unstructured streams, when a number-of-keys :TO: overall-size ratio is very small. An example would be a dictionary with one key, and value storing 500 kByte long string:

{"file": "a very very very ... long string, perhaps just encoded file"}

In such case - yes, a better aproach is to store it as a regular files.

So as always, it all depends on a particular use case :)

Upvotes: 1

Raja Ramachandran
Raja Ramachandran

Reputation: 456

Yes you can save JSON to mysql database. Mysql added the JSON datatype above 5.7 version.

Please refer http://www.mysqltutorial.org/mysql-json/

Upvotes: 5

glend
glend

Reputation: 1622

It would just be a BLOB and theres nothing inheriently wrong with that, its a supported datatype.

However, i would urge you to save the JSON to a file and keep a list of paths instead. simply because it will be quicker.

Upvotes: 0

Jaypal Sodha
Jaypal Sodha

Reputation: 2688

Yes, you can save JSON response inside the table itself.

But It will be in simple string format and parsing of JSON string required to be done at code level.

You can use CLOB for the same.

Upvotes: 0

Related Questions