Mongodb query for selecting embeded object

Question

I have a collection whose documents look like this

{'_id': ObjectId('5d7f4aa4d2394d86aacbfbe0'),
 'aeroplanes': [{'_id': ObjectId('5d7f4aa4d2394d86aacbfbd8'),
                 'capacity': 1442,
                 'flights': [{'_id': ObjectId('5d7f4aa4d2394d86aacbfbd7'),
                              'arrival_time': datetime.datetime(2010, 10, 8, 3, 26, 50),
                              'departure_time': datetime.datetime(1988, 6, 29, 14, 10, 52),
                              'gate_number': 6,
                              'seats': [{'_id': ObjectId('5d7f4aa4d2394d86aacbfbd6'),
                                         'price': 1779,
                                         'ticket': None,
                                         'type': 'A'}]}],

I want to select all the seats with None (null) type. The output I want is like:

'seats': [
   {'_id': ObjectId('5d7f4aa4d2394d86aacbfbd6'),
     'price': 1779,
     'ticket': None,
     'type': 'A'},
   {'_id': ObjectId('5d7f4aa4d2394566acfgbgt'),
    'price': 3546,
    'ticket': None,
    'type': 'A'}
]

I tried to follow this answer and wrote this:

airline_col.aggregate([
{"$match": {'aeroplanes.flights.seats.ticket': None}},
{"$project": {
    "seats" : { "$filter" : {
        "input": '$aeroplanes.flights.seats',
        "as": 'seat',
        "cond": {"$eq": ['$$seat.ticket', None]}
    }}
  }}
])

But it is just returning empty arrays. What will be the correct query?

Mongo playground query link: https://mongoplayground.net/p/Wl2l4IdJLT2

it will be real helpful for other to answer if you put you collection sample data and what you have done in query in here https://mongoplayground.net/ for mongo-query. — Saikat Chakrabortty, Sep 16 '19 at 09:22
You want only the seats data in response after query, and not fields like aeroplanes,flights etc? — sushant mehta, Sep 16 '19 at 11:33
Actually, I need both of the query types. In one type, only the seat info are needed and on the other type, I need all the parent information too. — Ajoy D, Sep 16 '19 at 12:31
It returns empty seats array too. Is there any way to modify the query so that it doesn't return the parent object if seats array is empty? — Ajoy D, Sep 16 '19 at 12:39

score 1 · Answer 1 · answered Sep 16 '19 at 13:16

So here is the final query after using $cond in $map and $filter stages

Input:

[
  {
    "_id": "5d7f4aa4d2394d86aacbfbe0",
    "aeroplanes": [
      {
        "_id": "5d7f4aa4d2394d86aacbfbd8",
        "capacity": 1442,
        "flights": [
          {
            "_id": "5d7f4aa4d2394d86aacbfbd7",
            "arrival_time": "2003-07-29",
            "departure_time": "1984-09-19",
            "gate_number": 6,
            "seats": [
              {
                "_id": "5d7f4aa4d2394d86aacbfbd6",
                "price": 1779,
                "ticket": null,
                "type": "A"
              }
            ]
          }
        ],
        "model": "Blue writer second all capital become."
      },
      {
        "_id": "5d7f4aa4d2394d86aacbfbdf",
        "capacity": 240,
        "flights": [
          {
            "_id": "5d7f4aa4d2394d86aacbfbde",
            "airport_id_as_dest": "5d7f49f1d2394d86aacb7d26",
            "airport_id_as_source": "5d7f49f1d2394d86aacb7cc9",
            "arrival_time": "2009-04-30",
            "departure_date": "2016-05-07",
            "gate_number": 2,
            "seats": [
              {
                "_id": "5d7f4aa4d2394d86aacbfbdd",
                "price": 1896,
                "ticket": {
                  "_id": "5d7f4aa4d2394d86aacbfbdc",
                  "agent_id": "5d7f49f1d2394d86aacb7cf2",
                  "boarding_pass": {
                    "_id": "5d7f4aa4d2394d86aacbfbdb"
                  },
                  "pnr": {
                    "_id": "5d7f4aa4d2394d86aacbfbda",
                    "name": "Breanna",
                    "passenger_id": "5d7f49f1d2394d86aacb7d3a",
                    "services": [
                      {
                        "_id": "5d7f4aa4d2394d86aacbfbd9",
                        "service": "Dawn "
                      }
                    ]
                  }
                },
                "type": "A"
              }
            ]
          }
        ],
        "model": "Stand system pattern write."
      }
    ],
    "name": "Raise billion order close.",
    "type": "Asd-1-3"
  }
]

Aggregate Pipeline:

db.collection.aggregate([
  {
    $project: {
      type: 1,
      name: 1,
      aeroplanes: {
        $filter: {
          input: "$aeroplanes",
          as: "a1",
          cond: {
            $ne: [
              {
                $map: {
                  input: "$$a1.flights",
                  as: "a2",
                  in: {
                    $cond: [
                      {
                        $eq: [
                          {
                            $filter: {
                              input: "$$a2.seats",
                              as: "a3",
                              cond: {
                                $eq: [
                                  "$$a3.ticket",
                                  null
                                ]
                              }
                            }
                          },
                          []
                        ]
                      },
                      "NO_DATA",
                      {
                        _id: "$$a2._id",
                        arrival_time: "$$a2.arrival_time",
                        departure_time: "$$a2.departure_time",
                        gate_number: "$$a2.gate_number",
                        model: "$$a2.model",
                        seats: {
                          $filter: {
                            input: "$$a2.seats",
                            as: "a3",
                            cond: {
                              $eq: [
                                "$$a3.ticket",
                                null
                              ]
                            }
                          }
                        }
                      }
                    ]
                  }
                }
              },
              [
                "NO_DATA"
              ]
            ]
          }
        },

      }
    }
  }
])

Output:

[
  {
    "_id": "5d7f4aa4d2394d86aacbfbe0",
    "aeroplanes": [
      {
        "_id": "5d7f4aa4d2394d86aacbfbd8",
        "capacity": 1442,
        "flights": [
          {
            "_id": "5d7f4aa4d2394d86aacbfbd7",
            "arrival_time": "2003-07-29",
            "departure_time": "1984-09-19",
            "gate_number": 6,
            "seats": [
              {
                "_id": "5d7f4aa4d2394d86aacbfbd6",
                "price": 1779,
                "ticket": null,
                "type": "A"
              }
            ]
          }
        ],
        "model": "Blue writer second all capital become."
      }
    ],
    "name": "Raise billion order close.",
    "type": "Asd-1-3"
  }
]

Link:https://mongoplayground.net/p/UrR8TTuMgDF

score 1 · Answer 2 · answered Sep 16 '19 at 18:40

Try this:

db.collection.aggregate(
[
    {"$unwind": "$aeroplanes"},
    {"$unwind": "$aeroplanes.flights"},
    {"$unwind": "$aeroplanes.flights.seats"},
    {"$match": {"aeroplanes.flights.seats.ticket": null}},
    {"$project": {"aeroplanes.flights.seats": 1, "_id": 0}}
]
)

https://mongoplayground.net/p/d3X0pX2Zkrl

Mongodb query for selecting embeded object

2 Answers2