Get just a few values from all Objects in MongoDB

Question

i have a question about getting specific data out of a mongo db. The Data in the DB are very big and there are many objects in it. The objects have the following architecture:

{
        "_id": ObjectId("XXXX"),
        "app_id": "XXXXXX",
        "title": "The title",
        "os": "OS",
        "crash": [{
                "crash_reason": {
                        "updated_at": "2018-06-28T03:39:47Z",
                        ...many values ...
                },
                "crashes": [{
                        ...many values ...
                },
                {
                        ...many values ...
                }],
                "status": "success",
        }, {
                "crash_reason": {
                        "updated_at": "2018-06-28T03:39:46Z",
                        ...many values ...
                },
                "crashes": [{
                        ...many values ...
                },
                {
                        ...many values ...
                }],
                "status": "success",
        }],
        "status": "success",
}

so... to get all object i am just using my db and then do this:

db.crashes.find()

But, i want to get all objects but only with specific data. The output should look like this (if possible).

{
        "_id": ObjectId("XXXX"),
        "app_id": "XXXXXX",
        "title": "the title",
        "os": "OS",
        "crash": [{
            "crash_reason": {
                    "updated_at": "2018-06-28T03:39:47Z" }
                 }]
}

Sadly i am new to mongo and try to find a solution with the documentation but with no success. i tryed many things, for example:

db.crashes.find([{$group:{app_id: "$app_id",title: "$title",os: "$os", crash: { $first: "$updated_at" }}}])
db.crashes.aggregate([{$group:{app_id: "$app_id",title: "$title",os: "$os", crash: { $first: "$updated_at" }}}])

is it even possible? I would also be satisfied with the following output :-D

{
        "_id": ObjectId("XXXX"),
        "app_id": "XXXXXX",
        "title": "the title",
        "os": "OS",
}

can someone please help me? :-)

Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — Ashh, Jul 13 '18 at 08:13

cornacchia · Accepted Answer · 2018-07-13T09:14:16.017

0

You need to project your data (https://docs.mongodb.com/manual/reference/operator/projection/positional/)

Try this out:

db.crashes.find({}, {"app_id": 1, "title": 1, "os": 1})

If you want to get the array element with the latest updatedAt value this is a possible pipeline:

db.crashes.aggregate([
  {$unwind: '$crash'},
  {$sort: {'crash.crash_reason.updatedAt': -1}},
  {$group: {
     _id: "$app_id",
     title: {$first: "$title"},
     os: {$first: "$os"},
     crash: {$first: "$crash"}
  }}
])

This will $unwind the crash array (i.e. it will create a document for each crash array element), then it will sort all the documents by the updatedAt date (latest first). Finally it will group all documents by app_id and take the first crash value, which will be the last thanks to the previous $sort stage.

If you want to cut down your data even more you can add a $project stage in the aggregation pipeline before or after the $group stage (for example you could cut out all irrelevant data in the first stage for better performance).

Take a look at the documentation for a better explanation (https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/)

edited Jul 13 '18 at 09:14

answered Jul 13 '18 at 08:15

cornacchia

473
2
9

wow so simple and it worked. thx for the link to the doc. And is there also a way to get the first "updated_at" or is that not possible? – FrostX Jul 13 '18 at 08:17
yes thank you. but i get a "The field 'title' must be an accumulator object" exception. I am trying to find out why – FrostX Jul 13 '18 at 08:58
ah sorry, you could use the `$first` operator on `title` and `os` too, I'll update the answer. – cornacchia Jul 13 '18 at 09:13
Hi, many thanks. it looks now nearly the same as the solution from Amrut. Sadly i get the same exception: zsh: bad math expression: operand expected at {: }' zsh: bad math expression: operand expected at {: {crash....' i am trying to understand whats happening and fix it hopefully. – FrostX Jul 13 '18 at 09:20
I don't think this is a problem with the query. How do you connect to the db? Running it in Robo3T works fine for me. – cornacchia Jul 13 '18 at 09:28
sorry, i made a mistake. Now it is working. MANY THX for the help :-) – FrostX Jul 13 '18 at 09:29

score 0 · Answer 2 · answered Jul 13 '18 at 08:56

0

Try below aggregate query to return latest subdocument data with expected fields,

db.crashes.aggregate([
  {$unwind: '$crash'},
  {$sort: {'crash.crash_reason.updatedAt': -1}},
  {$group: {
     _id: "$_id",
     app_id:{$first:"$app_id"},
     title: {$first:"$title"},
     os: {$first:"$os"},
     crash: {$first: "$crash.crash_reason"}
  }}
])

answered Jul 13 '18 at 08:56

Amrut Gaikwad

524
1
3
19

Hi, also thank you for the help. but i get exceptions: zsh: bad math expression: operand expected at `{: }' zsh: bad math expression: operand expected at `{: {crash....' Also trying to find the problem. – FrostX Jul 13 '18 at 09:04
sorry, i made a mistake. Now it is working. MANY THX for the help :-) – FrostX Jul 13 '18 at 09:30

Get just a few values from all Objects in MongoDB

2 Answers2