Getting only the first item for an array property in mongodb

Question

Given the following layout in a collection...

{
  vehicle_id: 1
  ,// bunch of properties I don't want
  ,vehicle: {
    mfg_year: 1928
    ,mfg_make: "Ford"
    ,mfg_model: "Model A"
    ,mfg_trim: "T-Bucket"
    ,// bunch of properties I don't want
    ,images: [
      {url:'...',...}
      ,...
    ]
  }
}

How would I return a result with only the above fields, and only the first result under images? I don't mind if the results are in one flattened object, with only the images being a nested object.

I've looked into the Aggregation Framework, which doesn't seem to match what I am looking for. I know I could do a map/reduce on the results set, or do a group on the listing_id, am just hoping to have a simpler query structure without needing to resort to group, or reduce.

If this isn't possible currently via the aggregation framework, a working group or map-reduce would be an acceptable answer.

EDIT: There are about 50+ properties that I don't want in the final result.. with the $slice directive, it seems I can't just specify the fields I want.

group / map-reduce queries shouldn't be used for 'real time' queries — Alex, Nov 19 '12 at 17:47
@alexjamesbrown I understand that, which is why I'm hoping for a more optimized query... $slice leads in the right direction though. — Tracker1, Nov 19 '12 at 18:06

Alex · Accepted Answer · 2012-11-19T18:09:44.717

7

How about:

db.vehicles.find({"vehicle_id":1}, {images:{$slice: 1}})

Source: http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields#RetrievingaSubsetofFields-RetrievingaSubrangeofArrayElements

Example

db.vehicles.insert({"vehicle_id": 1, "mfg_year": "1928", "mfg_make": "Ford", "mfg_model": "Model A", "images": [{"url":"www.a.com"}, {"url":"www.b.com"}, {"url":"www.c.com"}]})

db.vehicles.insert({"vehicle_id": 2, "mfg_year": "1999", "mfg_make": "BMW", "mfg_model": "Model B", "images": [{"url":"www.a.com"}, {"url":"www.b.com"}, {"url":"www.c.com"}]})

db.vehicles.insert({"vehicle_id": 3, "mfg_year": "1998", "mfg_make": "FMerc", "mfg_model": "Model C", "images": [{"url":"www.a.com"}, {"url":"www.b.com"}, {"url":"www.c.com"}]})


//now the query
db.vehicles.find({"vehicle_id":1}, {images:{$slice: 1}})

Output:

{
        "vehicle_id" : 1,
        "mfg_year" : "1928",
        "mfg_make" : "Ford",
        "mfg_model" : "Model A",
        "images" : [
                {
                        "url" : "www.a.com"
                }
        ]
}

EDIT

You can specify just the fields you want to return like this:

db.vehicles.find({"vehicle_id":1}, {"mfg_make":1, images:{$slice: 1}})

So, in this instance, only the mfg_make and images is returned.

Another....

db.vehicles.find({"vehicle_id":1}, {"mfg_make":1, "some_other_field":1, images:{$slice: 1}})

If this were a RDBMS, this query is equivalent to:

SELECT mfg_make, some_other_field FROM tblVehicles WHERE vehicle_id = 1

edited Nov 19 '12 at 18:09

answered Nov 19 '12 at 17:51

Alex

37,502
51
204
332

Should have been more clear... there are about 50 other properties that I don't want in the results... is there a way to only get the above properties, without 50+ false entries for the field selectors? – Tracker1 Nov 19 '12 at 18:03
'in the results' - is that in the array of images, or in the main body of 'vehicle'? – Alex Nov 19 '12 at 18:06
edited answer to cover how to choose what fields to return. It sounds like you have more fields to exclude, than to include, so it will be easier to list the ones to INCLUDE rather than EXCLUDE – Alex Nov 19 '12 at 18:11
Thank you... for some reason if there's only one field beyond the slice specified, it returned all fields. – Tracker1 Nov 19 '12 at 20:45
db.listings.find({"is_active":true}, {"vehicle_id":1, "vehicle.mfg_make":1, ..., "vehicle.images":{$slice: 1}}).limit(1) – Tracker1 Nov 19 '12 at 20:46
1

@Tracker1 The one field beyond is probably _id, which is projected unless explicitly excluded; however the above doesn't quite work if you only specify the slice; what you'd need (today) is `{images:{$slice:1}, images:1, _id:0}` – nik.shornikov Apr 30 '13 at 23:08

score 0 · Answer 2 · answered Nov 04 '20 at 15:42

With aggregation framework you have the operators $project and $first (available in MongoDB version 4.4 and above) to get the desired result:

db.collection.aggregate([
    { '$project': {
        'vehicle_id': 1,
        'vehicle': {
            'mfg_year': '$vehicle.mfg_year',
            'mfg_make': '$vehicle.mfg_make',
            'mfg_model': '$vehicle.mfg_model',
            'mfg_trim': '$vehicle.mfg_trim',
            'images': { '$first': '$vehicle.images' }
        }
    } }   
])

or with $arrayElemAt

db.collection.aggregate([
    { '$project': {
        'vehicle_id': 1,
        'vehicle': {
            'mfg_year': '$vehicle.mfg_year',
            'mfg_make': '$vehicle.mfg_make',
            'mfg_model': '$vehicle.mfg_model',
            'mfg_trim': '$vehicle.mfg_trim',
            'images': { '$arrayElemAt': ['$vehicle.images', 0] }
        }
    } }   
])

or with $slice

db.collection.aggregate([
    { '$project': {
        'vehicle_id': 1,
        'vehicle': {
            'mfg_year': '$vehicle.mfg_year',
            'mfg_make': '$vehicle.mfg_make',
            'mfg_model': '$vehicle.mfg_model',
            'mfg_trim': '$vehicle.mfg_trim',
            'images': { '$slice': ['$vehicle.images', 1] }
        }
    } }   
])

Getting only the first item for an array property in mongodb

2 Answers2