That in a nutshell represent regular tabular data of 2 columns and 1 row like this:
Is there any way to use $lookup operator to join those two collection based on _id from columns array in tables collection and based on the key name from table_rows collection? What i’m trying is to somehow join columns definitions (name, datatype, etc) along with cell values.
As you can see actual key name in card_rows collection is _id of column itself.
Ideally this would be single collection, but those tables can grow to hundred of columns and 10K of rows, so it is modeled as two collections to avoid unbound arrays in mongo.
The use of data value (like columns _id) as field name (in your table_rows) is usually a bad idea. With the attribute pattern the table_rows collection will avoid using dynamic value as field name like in
But then why don’t you simply completely remove the columns _id and use another array for your table_rows? The size of the data and direct 1-to-1 mapping between columns definition and values would be much simpler, much faster and must smaller.
Thank you very much @steevej . You dont know how much i appreciated your answer. Being not that experienced with mongodb, for quite sometime im trying to find some good-enough solution that would cover standard operation that user can do with regular spreadsheet or table of data, including ability to support:
sorting per columns
reordering of columns
support large tables (like 50 columns, 10 thousands rows)
Are you going to access a table without its table_rows? => rarely, if ever
Are you going to access a table_row without its table? => rarely, if ever
Im totally aware that data that should be queried together should be part of same collection, and that was my first attempt as explained in above referenced question. My concern about that approach is that if everything is in same collection, more or less i will hit unbound array problem with mongodb, correct?
The reasoning behind, solution i proposed here where dynamic object id in card_rows collection is actually columnId, is that i need ability to sort, filter and do other aggregate operation on card rows.
Now looking at your proposed solution it looks much more better.
In your opinion with your proposed solution, would these operations like grouping, sorting, etc, work with elements of array and does this come with some drawbacks in your opinion?
Again, really really thank you for your time and for your answer.
I saw the other thread and Satyam answer was more than appropriate.
For the record, I am an independent consultant. I do not design and code for free. I help, I point to resource, I bring questions to think about and I provide solutions to tricky aggregations. But I will not design and code for free. Why would my customer contract me if they can come here and have the work done for free?
A little bi surprised by your answer. I had absolutely nothing in mind more than just having discussion about two approaches, and seeking for other opinions as well, nothing more, which i guess community like this is all about. Anyway as i said earlier thank you very very much for your time and for your opinion, it helped really a lot. Really appreciated. Kind regards
was that if you access everything together most of the time you still risk having a huge array. A $lookup simply build an array of documents. So if rows are too big to fit in a single document they will also be too big when you $lookup.
But there is nothing wrong about limiting the number of columns and rows. I am sure there are some in Excell and LibreOffice.
Doing sort and filtering in documents is probably more efficient since you might be able to define indexes while sorting and filtering an array will always be a memory sort.
Thank you @steevej once again really, really appreciated.
In the end based on some testing and different approaches and based on some very good inputs by you and similar threads, i will go with two collections. One to keep data about tables and column definitions, another one to keep rows of the tables. Some sort of attribute pattern as you mentioned: