Store java object as json string in MySQL database?

Question

Performance wise, is it smart to do this? An example would be with Gson

Gson gson = new Gson();
String json = gson.toJson(myObject);

// store json string in sql with primary key
// retrieve json string in sql with primary key

I want to simplify the way i store and retrieve objects, instead of building and separating them into pieces and individual columns each time i store/retrieve from a database.

But my concern with using a json string is that the length may impact performance when the database fills up? Im not sure, this is why im asking.

user2864740 · Accepted Answer · 2015-03-15 03:25:41Z

1

There is not an issue with 'space' used or performance of such: MySQL will deal with that just fine.

That is, while the entire JSON chunk must be pulled/pushed for processing and changes MySQL will continue to handle such as best it ever did, even 'as the database fills up'.

However, there are problems with normalization and opaqueness of information when following this design. Databases are about information, but a blob of JSON is just .. a blob of JSON to an SQL database.

Because of this none of the data in the JSON can be used for relationships or queries nor can it participate in indices or constraints. So much for the "relational" part of the database..

Unless the JSON truly is opaque data, like the contents of a binary file, consider working on relevant normalization .. or switch to a (JSON) document-oriented database (eg. Raven, Redis, Couch, Mongo).

edited Mar 15, 2015 at 3:25

answered Mar 15, 2015 at 3:14

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

esycat · Accepted Answer · 2015-03-15 03:40:20Z

There is no space or performance issues with storing JSON strings. MySQL is capable to handle large blobs, if you need so.

The decision whether to store your data serialized into JSON or not should be based on how do you need to process this data. Relational databases, such as MySQL, suppose that you normalize the data and establish relationships between records. That said, in many cases it can be practical to do otherwise (i.e. store as JSON).

If you will store your data as JSON strings, you will not be able to effectively process this data using MySQL features, e.g. you cannot filter out or sort records by values stored as JSON. However, if you need only to store this data, while the processing is going to be done by the application code, it can be reasonable to use JSON.

As document-oriented databases like MongoDB become more popular, some of the traditional relational databases, such as PostgreSQL and MariaDB, recently also implemented native JSON support.

Collectives™ on Stack Overflow

Store java object as json string in MySQL database?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related