Hi. I am new to mongo, have a question about data model design.
I have a graph like structure need to represent in MongoDB. Can’t use graph db because of lookups needed with same db. Most use cases show that this is mostly directed graph, when traversing is happening from “child” to “parent”. This is kind of a dictionary/classifier master data. The path matters.
See the attached image. Using int as object Ids and dropping quotation marks for clarity.
I need to represent this in collections A and B.
Collection A will not grow much over time, however paths may be added later or removed, e.g. 9 may become child of 10, 8 might not belong to 5 anymore. Node themselves can not be deleted. Typically each node will have 1 “parent”, some will have 3-4 max.
Collection B will grow over time. Rough estimate is 1300 docs daily, about a million in 3 years. Documents in collection B have to store the original path A had at time of creation. Lets assume when entering document B, user will pick the A path in some fancy UI.
Except of A field, there will be C, D etc all referencing A collection in a similar way.
Typical queries in application needed (when I say find Bs, then additional filters will be applied on other B fields):
Q1) Find Bs, where A.Id=x, path does not matter
Q2) Find Bs, where A.Id=x, and path is precisely [1,4,8]
Q3) Get single B document, for each path in it’s A get the node details. E.g. for B2, need to get fields for A = 9
Q4) Get single B document, for each path in it’s A get the node details. E.g. for B2, need to get all fields for A 2,5,8.
Q5) Get single document B, project its As in a way to group by path. E.g. B1.A would look like {path: [1,3], result: [{Id: 7}, {Id: 10}]}
Q6) Find Bs, where any element in A path starts with[2,5]. E.g in this case B2 will be found.
Rarely used queries, but still
- From Collection A, find all ancestors on all paths for id = x, and their details. This can be done in loop in application, does not have to be a single query. So for id =10 I need to get details of 1,3,2,6
- Get immediate children of A. E.g. for A=2 it will be 5 and 6.
- Get children up to X level depth of A. E.g. for A=2, depth=2 it will be 5,6,8,10
I am thinking of using ‘array of ancestors’ representation for graph edges. https://docs.mongodb.com/manual/tutorial/model-tree-structures-with-ancestors-array/
Where in A.paths would be array of arrays of objectIds.
In B I would store A’s id and path as objectId
I would index Bs on A.Id and A.path.
I am wondering are there any better solutions (materialized path?) considering indexing and queries ?