Return specific array value field in aggregate

Question

I have a issue in MongoDB i'm trying to build a very complex aggregate query, and its work almost as i want it, but i still have trobles, and the problems is i need to move a spefiect field so i can use it later.

My aggregate look like this right now.

db.getCollection('travel_sights').aggregate([{
  '$match': {
    'preview.photo' : {
      '$exists':true
    },
    '_id': {
      '$in' : [ObjectId("5b7af9701fbad410e10f32f7")]
    }
  }
},{
  '$unwind' : '$preview.photo'
}, {
  '$lookup':{
    'from' : 'media_data',
    'localField' : '_id',
    'foreignField':'bind',
    'as':'media'
  }   
}])

and it will return data like this.

{
    "_id" : ObjectId("5b7af9701fbad410e10f32f7"),
    "preview" : {
        "photo" : {
            "id" : ObjectId("5b7affea1fbad441494a663b"),
            "sort" : 0
        }
    },
    "media" : [ 
        {
            "_id" : ObjectId("5b7affea1fbad441494a663b")
        }, 
        {
            "_id" : ObjectId("5b7b002d1fbad441494a663c")
        }, 
        {
            "_id" : ObjectId("5b7b00351fbad441494a663d")
        }, 
        {
            "_id" : ObjectId("5b7d9baa1fbad410de638bbb")
        }, 
        {
            "_id" : ObjectId("5b7d9bae1fbad410e10f32f9")
        }, 
        {
            "_id" : ObjectId("5b7d9bb11fbad441494a663e")
        }, 
        {
            "_id" : ObjectId("5b7d9bb41fbad4ff97273402")
        }, 
        {
            "_id" : ObjectId("5b7d9bb71fbad4ff99527e82")
        }, 
        {
            "_id" : ObjectId("5b7d9bbb1fbad410de638bbc")
        }, 
        {
            "_id" : ObjectId("5b7d9bbe1fbad410e10f32fa")
        }, 
        {
            "_id" : ObjectId("5b7d9bc11fbad441494a663f")
        }, 
        {
            "_id" : ObjectId("5b7d9bc41fbad4ff97273403")
        }, 
        {
            "_id" : ObjectId("5b7d9bc71fbad4ff99527e83")
        }, 
        {
            "_id" : ObjectId("5b7d9bca1fbad410de638bbd")
        }, 
        {
            "_id" : ObjectId("5b7d9bcd1fbad441494a6640")
        }, 
        {
            "_id" : ObjectId("5b7d9bd01fbad4ff97273404")
        }
    ]
}
{
    "_id" : ObjectId("5b7af9701fbad410e10f32f7"),
    "preview" : {
        "photo" : {
            "id" : ObjectId("5b7b002d1fbad441494a663c"),
            "sort" : 0
        }
    },
    "media" : [ 
        {
            "_id" : ObjectId("5b7affea1fbad441494a663b")
        }, 
        {
            "_id" : ObjectId("5b7b002d1fbad441494a663c")
        }, 
        {
            "_id" : ObjectId("5b7b00351fbad441494a663d")
        }, 
        {
            "_id" : ObjectId("5b7d9baa1fbad410de638bbb")
        }, 
        {
            "_id" : ObjectId("5b7d9bae1fbad410e10f32f9")
        }, 
        {
            "_id" : ObjectId("5b7d9bb11fbad441494a663e")
        }, 
        {
            "_id" : ObjectId("5b7d9bb41fbad4ff97273402")
        }, 
        {
            "_id" : ObjectId("5b7d9bb71fbad4ff99527e82")
        }, 
        {
            "_id" : ObjectId("5b7d9bbb1fbad410de638bbc")
        }, 
        {
            "_id" : ObjectId("5b7d9bbe1fbad410e10f32fa")
        }, 
        {
            "_id" : ObjectId("5b7d9bc11fbad441494a663f")
        }, 
        {
            "_id" : ObjectId("5b7d9bc41fbad4ff97273403")
        }, 
        {
            "_id" : ObjectId("5b7d9bc71fbad4ff99527e83")
        }, 
        {
            "_id" : ObjectId("5b7d9bca1fbad410de638bbd")
        }, 
        {
            "_id" : ObjectId("5b7d9bcd1fbad441494a6640")
        }, 
        {
            "_id" : ObjectId("5b7d9bd01fbad4ff97273404")
        }
    ]
}
{
    "_id" : ObjectId("5b7af9701fbad410e10f32f7"),
    "preview" : {
        "photo" : {
            "id" : ObjectId("5b7b00351fbad441494a663d"),
            "sort" : 0,
            "primary" : false
        }
    },
    "media" : [ 
        {
            "_id" : ObjectId("5b7affea1fbad441494a663b")
        }, 
        {
            "_id" : ObjectId("5b7b002d1fbad441494a663c")
        }, 
        {
            "_id" : ObjectId("5b7b00351fbad441494a663d")
        }, 
        {
            "_id" : ObjectId("5b7d9baa1fbad410de638bbb")
        }, 
        {
            "_id" : ObjectId("5b7d9bae1fbad410e10f32f9")
        }, 
        {
            "_id" : ObjectId("5b7d9bb11fbad441494a663e")
        }, 
        {
            "_id" : ObjectId("5b7d9bb41fbad4ff97273402")
        }, 
        {
            "_id" : ObjectId("5b7d9bb71fbad4ff99527e82")
        }, 
        {
            "_id" : ObjectId("5b7d9bbb1fbad410de638bbc")
        }, 
        {
            "_id" : ObjectId("5b7d9bbe1fbad410e10f32fa")
        }, 
        {
            "_id" : ObjectId("5b7d9bc11fbad441494a663f")
        }, 
        {
            "_id" : ObjectId("5b7d9bc41fbad4ff97273403")
        }, 
        {
            "_id" : ObjectId("5b7d9bc71fbad4ff99527e83")
        }, 
        {
            "_id" : ObjectId("5b7d9bca1fbad410de638bbd")
        }, 
        {
            "_id" : ObjectId("5b7d9bcd1fbad441494a6640")
        }, 
        {
            "_id" : ObjectId("5b7d9bd01fbad4ff97273404")
        }
    ]
}

and what you can se the last data have preview.photo.primary on it, and this field i want to return when i'm done with my aggregate query.

My final query look like this:

db.getCollection('travel_sights').aggregate([{
  '$match': {
    'preview.photo' : {
      '$exists':true
    },
    '_id': {
      '$in' : [ObjectId("5b7af9701fbad410e10f32f7")]
    }
  }
},{
  '$unwind' : '$preview.photo'
}, {
  '$lookup':{
    'from' : 'media_data',
    'localField' : '_id',
    'foreignField':'bind',
    'as':'media'
  }   
},{
  '$unwind':'$media'
},{
  '$project' : {
    'preview' : 1,
    'media': 1,
  }
}, {
  '$group': {
    '_id':'$media._id',
    'primary': {
      '$first':'$preview'
    }
  }   
}])

The problem here is when i want $preview return so i can find the primary about it, its allways only return the first where the value not exists, if i use $push the problem is i get every thing.

is there a way so i can pick the right primary value in my return? have trying $addFields to but whitout eny kind of lock.

Travel_sights data:

{
    "_id" : ObjectId("5b7af9701fbad410e10f32f7"),
    "city_id" : ObjectId("5b6d0cb6222d4c70b803eaeb"),
    "activated" : true,
    "deleted" : false,
    "url" : "url is here",
    "name" : "title of it here",
    "updated_at" : ISODate("2018-08-22T17:22:27.000Z"),
    "content" : "content here",
    "preview" : {
        "photo" : [ 
            {
                "id" : ObjectId("5b7affea1fbad441494a663b"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7b002d1fbad441494a663c"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7b00351fbad441494a663d"),
                "sort" : 0,
                "primary" : true
            }, 
            {
                "id" : ObjectId("5b7d9baa1fbad410de638bbb"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7d9bae1fbad410e10f32f9"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7d9bb11fbad441494a663e"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7d9bb41fbad4ff97273402"),
                "sort" : 0,
                "primary" : false
            }, 
            {
                "id" : ObjectId("5b7d9bb71fbad4ff99527e82"),
                "sort" : 0,
                "primary" : false
            }, 
            {
                "id" : ObjectId("5b7d9bbb1fbad410de638bbc"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7d9bbe1fbad410e10f32fa"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7d9bc11fbad441494a663f"),
                "sort" : 0
            }, 
            {
                "id" : ObjectId("5b7d9bc41fbad4ff97273403"),
                "sort" : 0,
                "primary" : false
            }, 
            {
                "id" : ObjectId("5b7d9bc71fbad4ff99527e83"),
                "sort" : 0,
                "primary" : false
            }, 
            {
                "id" : ObjectId("5b7d9bca1fbad410de638bbd"),
                "sort" : 0,
                "primary" : false
            }, 
            {
                "id" : ObjectId("5b7d9bcd1fbad441494a6640"),
                "sort" : 0,
                "primary" : false
            }, 
            {
                "id" : ObjectId("5b7d9bd01fbad4ff97273404"),
                "sort" : 0
            }
        ]
    }
}

3 sample foto bind data here:

{
    "_id" : ObjectId("5b7affea1fbad441494a663b"),
    "file-name" : "55575110311__0F115282-B5A0-4654-AA44-B7DC2C682992.jpeg",
    "options" : [ 
        ObjectId("5b6fb855222d4c70b8041093")
    ],
    "type" : "images",
    "files" : [ 
        {
            "width" : 70,
            "height" : 53
        }, 
        {
            "width" : 400,
            "height" : 300
        }, 
        {
            "width" : 800,
            "height" : 600
        }, 
        {
            "width" : 1600,
            "height" : 1200
        }
    ],
    "bind" : [ 
        ObjectId("5b7af9701fbad410e10f32f7")
    ]
}
{
    "_id" : ObjectId("5b7b002d1fbad441494a663c"),
    "file-name" : "55575110748__E7B07EFD-9F7E-40D6-8B57-38F708E4C0C0.jpeg",
    "options" : [ 
        ObjectId("5b6fb855222d4c70b8041093")
    ],
    "type" : "images",
    "files" : [ 
        {
            "width" : 70,
            "height" : 53
        }, 
        {
            "width" : 400,
            "height" : 300
        }, 
        {
            "width" : 800,
            "height" : 600
        }, 
        {
            "width" : 1600,
            "height" : 1200
        }
    ],
    "bind" : [ 
        ObjectId("5b7af9701fbad410e10f32f7")
    ],
    "description" : "this is secoudn demo!",
    "title" : "demo 3"
}
{
    "_id" : ObjectId("5b7b00351fbad441494a663d"),
    "file-name" : "paris2.jpg",
    "options" : [ 
        ObjectId("5b6fb855222d4c70b8041093")
    ],
    "type" : "images",
    "files" : [ 
        {
            "width" : 70,
            "height" : 53
        }, 
        {
            "width" : 400,
            "height" : 300
        }, 
        {
            "width" : 800,
            "height" : 600
        }, 
        {
            "width" : 1600,
            "height" : 1200
        }
    ],
    "bind" : [ 
        ObjectId("5b7af9701fbad410e10f32f7")
    ],
    "description" : "this is a demo1 :)",
    "title" : "demo"
}

why you made a $unwind to preview at second stage of your first query? — Ashh
– Ashh, Commented Aug 24, 2018 at 18:10
i do this becure i need sort, primary values in my bind options (preview.photo.$.sort and preview.photo.$.primary) i don't know how else to get it later. — ParisNakitaKejser
– ParisNakitaKejser, Commented Aug 24, 2018 at 18:13
From here it seems to difficult what your are trying to do or achieve... It would be better if you post your initial sample collection and the output you want. — Ashh
– Ashh, Commented Aug 24, 2018 at 18:22
instead of $unwind $preview.photo add this to your second stage { $addFields: { "preview.photo": { $arrayElemAt: [ { $filter: { input: "$preview.photo", as: "photo", cond: { $ne: [ "$$photo.primary", undefined ] } } }, 0 ] } } } — Ashh
– Ashh, Commented Aug 24, 2018 at 18:39
very nice, never seen this before, can you explain with a awsner? :) — ParisNakitaKejser
– ParisNakitaKejser, Commented Aug 24, 2018 at 19:14

Ashh · Accepted Answer · 2018-08-24 19:39:24Z

1

You can filter out the element from the array where the primary field exists using $filter aggregation and then easily $group with the media._id field and get the $first document value.

Finally your query will be

db.getCollection("travel_sights").aggregate([
  { "$match": {
    "preview.photo" : { "$exists":true },
    "_id": { "$in" : [ ObjectId("5b7af9701fbad410e10f32f7") ] }
  }},
  { "$addFields": {
    "preview.photo": {
      "$arrayElemAt": [
        { "$filter": {
          "input": "$preview.photo",
          "as": "photo",
          "cond": { "$ne": [ "$$photo.primary", undefined ] }
        }}, 0
      ]
    }
  }},
  { "$lookup":{
    "from" : "media_data",
    "localField" : "_id",
    "foreignField": "bind",
    "as": "media"
  }},
  { "$unwind":"$media" },
  { "$project" : { "preview" : 1, "media": 1, }},
  { "$group": {
    "_id": "$media._id",
    "primary": { "$first": "$preview" }
  }}  
])

answered Aug 24, 2018 at 19:39

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ParisNakitaKejser Over a year ago

its wirdt, i still return false in it, i thought its return the resualt, but i toke wrong, i have add the main data + 3 sample of binding if its helping.

Ashh Over a year ago

so do you want the result where $preview.photo.primary is equal to true??

ParisNakitaKejser Over a year ago

i want the current value of $preview.photo.primary for each photo somtime its true, false or not exists

Ashh Over a year ago

How do know current value for $preview.photo.primary... Is there any criteria ? please post your sample output as well

ParisNakitaKejser Over a year ago

i'm $unwind before to split all $preview.photo up, and then i do $lookup to join $preview.photo.id with $media._id here i got my data, and group it last to only get it one time, and when i $unwind i can se id, sort and primary on every $preview.photo but when i later group its gone and can't get access to it agin.

|

Collectives™ on Stack Overflow

Return specific array value field in aggregate

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related