Is it a bad practice to have a large list of json objects in a database table column?

pachocastillosr

Hi,

My app does some inventory updates where some products are retrieved as json objects from an external api.

I have a database table called "inventory update" which contains a column called "products_json" which is the list of all the products involved in that specific "inventory update" (so it is a list of json objects).

Some inventory updates can involve thousands of products. Is it a bad idea to have this database structure (because the products_json column might contain thousands of json objects)?

Pd: I have already developed my app functionality with this structure and changing it would be a tough work but I need to build something scalable. So if this structure is going to cause crashes or timeouts, then its better for me to to change it now that I haven't launched.

Please let me know if you think I should change it or if Xano can handle this kind of data records with this long lists of json objects.

Thanks!

arturosanz

I would say that it depends on what you will do next with the data stored in that json list field. If you want to perform search queries on this field, then it's much better to store the information in a separate linked table.

If you just want to retrieve and show the content, then the only issue to consider would be the amount of memory required to retrieve thousands of records at once without pagination. Even if Xano could do that, the frontend would crash very likely.

Keep in mind that when you read a record you retrieve the whole content of the fields you have selected, and you won't be able to ask for just a part of the json list field content.

In conclusion: I would never recommend to store more than aprox 100 items in any list field.

pachocastillosr

Is there a limit to how many json objects you can store in a list?

Pawel Magdanski

Agree with @arturosanz. I think I remember a talk during office hours that the rule of thumb regarding performance (depends on case of course) would be not to put more than a 1000 objects in a nested list.

pachocastillosr

@arturosanz @Pawel Magdanski thanks for the insights! It seems like I'll probably re structure everything then!