mql - MongoDB Cross-Collection Query

Question

Assuming the following structure:

Assets

{
    "_id" : LUUID("d34a3fed"),
    "name" : "A",
    "records" : [ 
        LUUID("3627f3ac"), 
        LUUID("80e9d125"), 
        LUUID("4d5e8af5"), 
        LUUID("17593a39"), 
}

Records

{
    "_id" : LUUID("3627f3ac"),
    "Fields" : [ 
        {
            "Name" : "foo",
            "Value" : "bar",
        } 
    ],
}

My goal is to use a find() or aggregate() to cross-reference the two collections above. The two collections share the LUUID values.

{"records": "LUUID("3627f3ac")"}

{"_id": "LUUID("3627f3ac")"}

Ultimately retrieving the:

{"Fields.Name": "foo"}

name of the Records collection

Answer 1

Maybe something like this:

mongos> db.records.find()
{ "_id" : ObjectId("5ff8ccf9e0f1b975b90d7a86"), "fields" : [ { "name" : "foo", "value" : "bar" } ] }
{ "_id" : ObjectId("5ff8ccf9e0f1b975b90d7a87"), "fields" : [ { "name" : "foo2", "value" : "bar2" } ] }
{ "_id" : ObjectId("5ff8ccf9e0f1b975b90d7a88"), "fields" : [ { "name" : "foo3", "value" : "bar3" } ] }
mongos> db.assest.find()
{ "_id" : ObjectId("5ff8cd72e0f1b975b90d7a87"), "name" : "A", "records" : [ ObjectId("5ff8ccf9e0f1b975b90d7a86"), ObjectId("5ff8ccf9e0f1b975b90d7a87") ] }
mongos> db.assest.aggregate([   { $lookup:{ from:"records" , localField:"records" , foreignField:"_id" , as:"match"    }  } , {$unwind:"$match"} , {$unwind:"$match.fields"}   ,{$project:{ "Fields_name":"$match.fields.name" ,_id:0}}   ])
{ "Fields_name" : "foo" }
{ "Fields_name" : "foo2" }
mongos>