How to project specific elements in an Array field in MongoDB?

Question

I have this data in MongoDB:

{
"_id" : ObjectId("5c7e459f875ea5548de25722"),
"Autos" : [
    {
        "_id" : ObjectId("5cad9759e1c3895999adaceb"),
        "deleted" : 1,

    },
    {
        "_id" : ObjectId("5cad9a8be1c3895999adacef"),
        "deleted" : 0,

    },
    {
        "_id" : ObjectId("5cad9aa4e1c3895999adacf0"),
        "deleted" : 0,
    }
]
    }

{
"_id" : ObjectId("5c7e45e9875ea5548de25724"),
"Shoemaking" : [
    {
        "_id" : ObjectId("5cad9770e1c3895999adacec"),
        "deleted" : 1,
    },
    {
        "_id" : ObjectId("5cad9a5de1c3895999adaced"),
        "deleted" : 0,
    },
]

I want to basically select * from table where deleted = 0
show where the deleted records are equal to 0.

Here is what I have tried so far:

db.rental.find({"Autos.deleted":{$ne: 1}}).pretty()

db.rental.find({"Autos":  {$elemMatch: {deleted: 1 } } } ).pretty()

db.rental.find({"Autos.deleted": 0},{"Autos": {$elemMatch: 
{deleted:0}}});

But none of the above work for me. What am I doing wrong?

Desired output:

{
    "_id" : ObjectId("5c7e459f875ea5548de25722"),
    "Autos" : [
        {
            "_id" : ObjectId("5cad9a8be1c3895999adacef"),
            "deleted" : 0,

        },
        {
            "_id" : ObjectId("5cad9aa4e1c3895999adacf0"),
            "deleted" : 0,
        }
    ]
        }

        {
          "_id" : ObjectId("5c7e45e9875ea5548de25724"),
          "Shoemaking" : [
              {
                  "_id" : ObjectId("5cad9a5de1c3895999adaced"),
                  "deleted" : 0,
              },
          ]
}

I want the output to be something like the above, but all the queries I have tried so far either select only one record of Array or select nothing at all.

You want to seperate records or the same format with no deleted? — theboringdeveloper
– theboringdeveloper, Commented Apr 10, 2019 at 9:02
@GursheeshSingh I want to find all records but not where deleted record=1 in sql it would be something like select * from table where deleted != 1. Thanks — Saqy G
– Saqy G, Commented Apr 10, 2019 at 9:10
@GursheeshSingh here I have updated the question with desired output thanks — Saqy G
– Saqy G, Commented Apr 10, 2019 at 9:55
You have to unwind the array and then use equal condition to get the records and then format the documents according to your wish. — theboringdeveloper
– theboringdeveloper, Commented Apr 10, 2019 at 11:42

Sreeragh A R · Accepted Answer · 2019-04-10 13:29:16Z

1

db.rental.aggregate([
   {
      $project: {
         Autos: {
            $filter: {
               input: "$Autos",
               as: "auto",
               cond: { $eq:["$$auto.deleted",0] }
            }
         }
      }
   }
])

edited Apr 10, 2019 at 13:29

answered Apr 10, 2019 at 9:39

Sreeragh A R

3,0413 gold badges32 silver badges58 bronze badges

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

2 Comments

Saqy G Over a year ago

Thanks @Seeragh your answer is the closest now I can see the record with deleted 0 but it only shows the first record of the array not the second. It is more like select top 1 from table where deleted = 0. How to view all other records with deleted=0

Saqy G Over a year ago

@seeragh that was awesome it worked as expected. Thank you !! after all the answer it finally worked. Thanks to all

Raja Sekar · Accepted Answer · 2019-04-10 09:05:24Z

0

db.rental.find({ "Autos.deleted": { $eq: 0 } } )

answered Apr 10, 2019 at 9:05

Raja Sekar

2,14017 silver badges23 bronze badges

1 Comment

Saqy G Over a year ago

Hey Raja thanks for the reply I have already tried that it also gives me record which where deleted = 1

Bipin Shrestha · Accepted Answer · 2019-04-10 09:14:49Z

0

use $elemMatch to match contain inside array. mongodb-$elemMatch

db.rental.find({
      Autos: {
        $elemMatch: {
          deleted: 0
        }
      }
    })

answered Apr 10, 2019 at 9:14

Bipin Shrestha

4114 silver badges16 bronze badges

1 Comment

Saqy G Over a year ago

Thanks Bipin for reply I tried your query its not filtering out delete=1 instead its just displaying whole Autos array same as @Raja sekar reply. Now as for as I think its either the problem with 0 and 1 approach or projection

Collectives™ on Stack Overflow

How to project specific elements in an Array field in MongoDB?

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related