How best to use JSON in Bubble Database - Seeking Guidance for Refactoring

[johndurso]

I’m considering restructuring some core functionality of my app because it’s fairly slow and DB intensive, and I think JSON might be a good solution. I hoping for some guidance to make good decisions and understand limitations up front.

The function I’m refactoring is essentially storing and displaying table data, where the number of rows and columns are both variable/unknows before display. This is done for Products, and each will have a different number of rows/columns (these are determined by the site admin and will change, though not often). Right now, this is how it looks when a User is editing:

You can see that there are headers, and columns are of varying types that require varying inputs, for example multiselect, dropdown, text, etc. Right now I’m not using any table plugin, this is just 2 nested RGs - a RG of rows, each row containing an RG of fields. Within a table, the rows will never have different numbers of cells.

Right now I have tables for:

• Product Group - corresponds to the overall grouping and has some other data
• Product Instances - corresponds to rows
• Questions - corresponds to columns types and includes info of field and options
• Answers - corresponds to the actual input in each cell

I also need to track all revisions. That doesn’t mean every field change, but the User will click “New Rev”, make a set of edits, then click Save, and that updated data becomes the most recent rev. I need to be able to still display all previous save points as revisions by number.

If you’ve read this far thank you!

Now, here’s what I’m considering:

Option 1
A JSON object to hold everything for each revision. Something like:

Option 2
Two JSON objects with each revision, one defining the input data and one defining the column data. Something like:

Right now it seems like Option 2, even though it’s using 2 different objects (or perhaps because it is) would be the simpler and more robust option.

My questions are:
Any drawbacks or advantages I should consider with either of these options?
Is there another way to do this I’m not considering?
Is it possible / performant to look for fields within this JSON if it’s stored as a string in Bubble’s DB?
Am I crazy?

Thank you so much for reading!

[keith]

You won’t be able to search these things. This way lies madness, given Bubble’s lack of support for objects beyond Things and API response objects. What do you intend to do, write a plugin for each modeled JSON-modeled thing you want to expose? Or create API calls for each JSON-modeled Thing?

I know that certain people DO stuff like this, but there’s no benefit to doing this if you’re using Bubble’s internal database, IMO.

[bubble.trouble]

First, if you haven’t already done this I would try to identify where the performance bottleneck is because you might find a way to optimize the grid if performance is your only problem. You could pull out various parts of your search to find out if the other parts load quickly or where exactly the slowness is occurring in your grid/search. If you have exhausted all of your options in terms of optimizations then I would offer you this response for whatever it is worth.

1. Drawbacks - Yes - There are a number of drawbacks to storing data as JSON.
   a. Performance is one - storing data as a string is, generally speaking, not as performant as storing data as a data type.
   b. Ability to search on specific fields - still possible but not as easy
   c. Updating data is more complex.
2. Another way - Yes, I’m sure this problem could be solved in a variety of ways. I’ll make mention of one way below
3. Is it possible / performant - Possible yes, performant, see 1 abc. above.
4. Are you crazy? I’m not licensed to answer this question but I’m guessing by the clarity of the questions, no.

The idea of storing in JSON is a pretty common need for one reason or another which is why there have been quite a number of solutions developed that will do just that, in a very performant way. e.g. Mongodb, Amazon DocumentDb, Cosmos Db. All of these and a variety of others can be configured to work with Bubble.

Generally my rule of thumb is this. If you can accomplish something easily in Bubble use Bubble and only Bubble. If you find something that is just not working for your specific app or you are having problems specifically related to the database then use a different solution for the backend and stick with Bubble on the frontend.

(EDIT)
Also I would look into this as an alternative to the RG if you end up storing the data as JSON because it wouldn’t require a translation: The javascript spreadsheet

[johndurso]

@keith @bubble.trouble thanks for the input.

The catch here that made me think this might be viable is that I’m not ever going to actually try and search on anything that I’m planning on putting into JSON. There will be Products which I will search, and these Products will display this JSON, but I won’t need to reference/search any particular row or cell. I’ll just to, whenever a Product is displayed, pull the JSON and display it in a table, allow users to edit that table, then save the updated JSON back to Bubble.

Also regardless of if I use JSON or not, the way I’m doing things now has to change. It’s worked but it’s so fragile it’s difficult to modify because I have a lot of fancy logic to tell which fields belong to which revisions because I’m only creating new fields if they’re updated.

Anyway it’s a pain and this is somewhat the core of my app, so I don’t mind going with MongoDB or one of the others. I’ve used MongoDB in the past. Of course, I’d prefer to stay with Bubble but I’m willing to go the extra bit for performance if necessary.

Again, appreciate the incites, curious if the fact that I don’t need to query the data in the JSON would change opinions.