Get only selective column in MongoDB

Question

Display only selective columns in collection

{
   "_id": ObjectId("52f4a5ed07b0f7106a9d5e4c"),
   "FM_ID":"1253",
   "passwd": ".aDoSkljf_h+6589q76w3rJK",
   "Area": {
        "0": {
                "X": "53543500",
                "Y": "14031500",
                "images": {
                            "0": {
                                    "ImgId": ObjectId("52f4a5a107b0f7106a9d1a84"),
                                    "ImageType": NumberInt(15) 
                                },
                            "1": {
                                    "ImgId": ObjectId("52f4a5a107b0f7106a9d1a87"),
                                    "ImageType": NumberInt(2) 
                                }
                            } 
            },
        "1": {

                "X": "53543500",
                "Y": "14094572",
                "images": {
                            "0": {
                                    "ImgId": ObjectId("52f4a5a107b0f7106a9d1aa8"),
                                    "ImageType": NumberInt(15) 
                                },
                            "1": {
                                    "ImgId": ObjectId("52f4a5a107b0f7106a9d1aab"),
                                    "ImageType": NumberInt(2) 
                                }

                            } 
                },
        "2": {
                "X": "53480464",
                "Y": "14031500",
                "images": {
                            "0": {
                                    "ImgId": ObjectId("52f4a5a107b0f7106a9d1acc"),
                                    "ImageType": NumberInt(15) 
                                }
                            } 
            }

        }
}

I want to display only selective data in collection

BasicDBObject have following fields

    B.append("FM_ID", 1);
    B.append("_id", 1);
    B.append("Area.$.$.images$.$.ImageType", 1);

My requirement is only display the FM_ID,_id,and ImageType

Note that you have multiple `images` within multiple `Area` fields. Is there one that you want to get or **all** of them — Neil Lunn, Feb 10 '14 at 09:17
Yes My need is only filter the particular image based upon the image type. The Area also collection in that each object have this X,Y variable and Images Collection [ in images have many Image Object each Image object have Image ID and image Type ] can you tell how to design the scheme and filter only the matching Image Type data of ( FM ID,_id,Image Type) — user3283282, Feb 11 '14 at 12:15
Okay, Good. I kind of went with that in the answer, yet also the partial aggregation statement would put things into a state where you can filter on both of your nested arrays as I put in the Schema redesign. Take a look at the sample document there and read the linked post. The $unwind statements used are possible because the fields no-longer contain static sub-documents but arrays. Much more flexible. Follow the $match link and play with the sample document. — Neil Lunn, Feb 11 '14 at 12:20

score 0 · Answer 1 · answered Feb 10 '14 at 09:14

For getting the projection of fields, you should pass the DBObject for projection,

DBCursor cursor = collection.find(query, projectionQuery);

The projection is The DBObject in form of key-value pair. where,

key is the name of field you want to project. value can be either 0 or 1. 0 - means exclude the particular column from result set. 1 - means include the particular column in result set.

For more info, see here.

You can limit the fields tobe returned from an array using 'elemMatch' from projection query.

For more info.

This type of projection will not work. The question says (though not well) **only** those three fields. There is another way to do this. — Neil Lunn, Feb 10 '14 at 09:19

score 0 · Answer 2 · edited May 23 '17 at 11:57

Firstly, your schema is almost certainly not what you want to be able to query in any meaningful way. Everything is in sub-documents with 'pseudo-numeric' keys, and that's a sure sign that you actually want arrays and you do want them.

So, I'll consider the rest of the answer on that you absolutely change the format of your schema as shown in the below example. The reasons for this I went to some length to explain in this post.

{
    "_id": ObjectId("52f4a5ed07b0f7106a9d5e4c"),
    "FM_ID":"1253",
    "passwd": ".aDoSkljf_h+6589q76w3rJK",
    "Area": [
        {
            "X": "53543500",
            "Y": "14031500",
            "images": [
                {
                    "ImgId": ObjectId("52f4a5a107b0f7106a9d1a84"),
                    "ImageType": NumberInt(15) 
                },
                {
                    "ImgId": ObjectId("52f4a5a107b0f7106a9d1a87"),
                    "ImageType": NumberInt(2) 
                }
             ]
        },
        {

            "X": "53543500",
            "Y": "14094572",
            "images": [
                 {
                     "ImgId": ObjectId("52f4a5a107b0f7106a9d1aa8"),
                     "ImageType": NumberInt(15) 
                 },
                 {
                     "ImgId": ObjectId("52f4a5a107b0f7106a9d1aab"),
                     "ImageType": NumberInt(2) 
                 }
             ]
         },
         { 
             "X": "53480464",
             "Y": "14031500",
             "images": [
                 {
                     "ImgId": ObjectId("52f4a5a107b0f7106a9d1acc"),
                     "ImageType": NumberInt(15) 
                 }
              ] 
          }

     ]
 }

Now with this we can do many more meaningful things, as was covered in the other answer. But for this one, since you have not told us a lot more than this, we can retrieve the data as just the three fields you mention ( _id, FM_ID, ImageType ).

As this cannot be done in standard query projection and since you likely want to match certain values, the best approach is to run through the aggregation pipeline. There's plenty of Java documentation on this (all just DBObject anyway) but this is the standard shell form:

db.collection.aggregate([
    {$unwind: "$Area" },
    {$project: { FM_ID: 1, ImageType: "$Area.images.ImageType" }},
    {$unwind: "$ImageType"}
])

So this way we get only the three fields that you want in your response.

If you are looking for specific entries out of the document(s) structure, look at the $match pipeline operator.

Get only selective column in MongoDB

2 Answers2

Linked