mongodbstudio3t

Studio 3T select all array values in an array column


I have a Mongo collection with the current schema: (simplified)

{ 
    "_id" : ObjectId("55a94615a243a426db43d81e"), 
    "property_name" : "My Main Property", 
    "domain_list" : [
        "mynumber1url.com",
        "mynumber2url.com",
        "mynumber3url.com"
    ]
}

I want to query this collection and return all the elements of the domain_list along with the property_name associated with it like:

mynumber1url.com  My Main Property
mynumber2url.com  My Main Property
mynumber3url.com  My Main Property
mynumber4url.com  My Other Property
...

an aggregate query would be acceptable, but I'd rather be able to do it with just a single query/projection


Solution

  • A single $unwind stage will be enough:

    db.collection.aggregate([
        {$unwind: '$domain_list'}
    ])
    

    It will result in:

    [{ 
        "_id": ObjectId("55a94615a243a426db43d81e"), 
        "property_name" : "My Main Property", 
        "domain_list" : "mynumber1url.com"
    }, { 
        "_id": ObjectId("55a94615a243a426db43d81e"), 
        "property_name" : "My Main Property", 
        "domain_list" : "mynumber2url.com"
    }, { 
        "_id": ObjectId("55a94615a243a426db43d81e"), 
        "property_name" : "My Main Property", 
        "domain_list" : "mynumber3url.com"
    }]
    

    Or if you want to have it in a single string (I'm not sure about the format):

    db.getCollection('x').aggregate([
        {$unwind: '$domain_list'},
        {$project: {_id: {$concat: ['$property_name', ' ', '$domain_list']}}}
    ])
    

    Which will result in:

    [
      {"_id": "My Main Property mynumber1url.com"},
      {"_id": "My Main Property mynumber2url.com"},
      {"_id": "My Main Property mynumber3url.com"}
    ]