Gathering data from JSON fields in a relational database

Question

I'm working on a small e-commerce solution which uses MySQL. At first there was an initiative by the boss to use MongoDB but I suggested relational database because there are lot entities with relations between them. He then told me to just store everything in form of JSON so we can easily add new attributes. Now we have field products in the table orders which is basically a text field containing JSON and it's content looks like this:

{
 "<product 1 hash>": {
 "attribute1": "value1",
 "attribute2": "value2",
 "vat": "0.25",
 "price": "300"
 },
 "<product 2 hash>": {
 "attribute1": "value1",
 "attribute2": "value2",
 "vat": "0.25",
 "price": "270"
 }
}

Fast forward to now, client is looking for statistics about best selling products / types of products and sets of products, as well as other data about products. Now I think this would be a huge performance issue once they reach bigger number of orders because we would need to;

1. Get all orders
2. Parse JSON from the products field
3. Look for the products we need

What would be a good way to improve performance of this solution? I saw that MySQL has a support for JSON fields but I never worked with it before (I always used MongoDB when I had to design non-relational databases).

• Jon Raynor: Export out your JSON data into something that is more "queryable"
• Ewan: use the json data type mysqltutorial.org/mysql-json

Answer 1

I suggested relational database because there are lot entities with relations between them.

So logically-related Entities call for a Relational database.

Good thinking.

He then told me to just store everything in form of JSON so we can easily add new attributes.

And that's where things went wrong.

Storing big blobs of "stuff" in a database is fine, just as long as you don't need to then pull those "blobs" apart to work with them. That's exactly what you're having to do here.

A properly organised, relational database would be able to support all of their needs and more. I suspect you'll struggle to do so with what you've got.

What would be a good way to improve performance of this solution?

You'll need to get the data out of those JSON "blobs" and into something more sensibly queryable. Then, having done so, show your boss how much better (i.e. more performant) that solution is and use it as a case to redesign the whole database properly.

OK, modern database do have support for handling JSON natively, but this will always be slower than using your relational database properly.

Answer 2

Now I think this would be a huge performance issue....

Stop. Do not waste time solving performance problems that you do not have. The link Ewan posted indicates that MySQL allows for JSON-querying. Use that for as long as you can. All the big relational databases now offer JSON support to a surprising degree, because they all understood that they were about to get eaten alive by the NoSQL explosion. Just use JSON-aware queries, and if you ever actually develop a performance problem, then look to the other normal database optimization tricks before trying to pull that JSON out and transform it into relational structures.

• Kayaman: NoSQL was never about replacing RDBMS, except to those who didn't really grok databases. It results in situations like this, where a RDBMS is an excellent solution, but someone is so excited about NoSQL that they end up with poor design.