MySQL with MongoDB

We recently ran into a potential data issue at scale due to how we are using JSON columns in MySQL. As a result, we decided to investigate using MongoDB alongside our current MySQL implementation.

Admittedly, so far, it has not been pleasant as re-writing the backend functions has proved challenging when doing previously simple tasks.

I am curious to understand if MongoDB will actually provide us any tangible benefit over continuing to use JSON columns in SQL.

Essentially we envisage at scale having somewhere in the region of 250,000 users on the platform. Users would log data daily where an object in the JSON array represents a days data. We need to be able to search this array for specific objects and run analysis on the data within the object.

Another example is a JSON array where each object presents a specific item, that has data logged to it daily. As a crude example (as I can't share specifics around the product) is an object may be a food, and within the object we log to a nested array each time the food is eaten. We would want to search for that specific food and return all the nested objects for when it was eaten to provide analysis on the front end.

Can anyone see a compelling reason to move our JSON over to Mongo over continuing to use JSON columns in SQL?

Our originally thinking was we could make use of indexing in Mongo but also write queries to only return the specific objects from each document, rather than returning the entire array as we do currently and filtering in PHP before passing it to the front end (Vue).

Thanks