Query to retrieve all the lines involved in a documents

Question

I have a structure with document, and lines. A line has a reference to it's document. However some lines can also have a reference to another line.

I want to make a query to retrieve all the lines involved in a documents (meaning lines directly linked, and the referenced lines).

Example

{_id:1, doc:1 },
{_id:3, doc:1, linkedLine:4},
{_id:4, doc:2 },
{_id:5, doc:2 },

I would like to obtain

linesOfDoc(1) = {_id:1, doc:1},{_id:3, doc:1, linkedLine:4},{_id:4, doc:2 }

I could be done getting first lines with doc=1, the doing a loop and getting the linked lines if present.

But is that possible to do this in one mongodb query ?

Regards

Answer 1

You can not do joins with mongo, exactly as you would do with sql, but you can get close with aggregation pipeline.

You got all the data in one query, but you need to flatten it farther to get the exact result you specified.

MONGO> db.playground.find()
{ "_id" : 1, "doc" : 1 }
{ "_id" : 3, "doc" : 1, "linkedLine" : 4 }
{ "_id" : 4, "doc" : 2 }

MONGO> db.playground.aggregate([{ $lookup: { from: "playground", localField: "linkedLine", foreignField: "_id", as: "embeddedLinkedLine"}}, { $match: { doc: <id of the document youre looking for> }}])
{ "_id" : 1, "doc" : 1, "embeddedLinkedLine" : [ ] }
{ "_id" : 3, "doc" : 1, "linkedLine" : 4, "embeddedLinkedLine" : [ { "_id" : 4, "doc" : 2 } ] }