I want to get the inner entries from a MongoDB data but the outer entries sticks with the data I need

user_6148841 0
Viewed 36

I have a MongoDB data that looks like this:

{
   "_id":"ObjectId(""607ddd26145c153476497e3f"")",
   "official number":NumberLong(11198),
   "mariners":[
      {
         "name":"John Williams",
         "last_ship_name":"Majestic of Aberystwyth",
         "this_ship_joining_port":"Llanelly",
         "age":NumberLong(35),
         "this_ship_leaving_cause":"Remains on board",
         "this_ship_joining_date":"1850-09-21",
         "place_of_birth":"Cardigan",
         "additional_notes":"Remains on board",
         "signed_with_mark":"N",
         "this_ship_capacity":"Mate",
         "last_ship_leaving_date":NumberLong(1868),
         "last_ship_port":"Cardiff"
      },
      {
         "home_address":"No info",
         "name":"Edward Jones",
         "last_ship_name":"Naval Reserve",
         "this_ship_joining_port":"Liverpool",
         "this_ship_leaving_date":"01/03/1879",
         "age":"No info",
         "this_ship_leaving_cause":"Discharged",
         "this_ship_leaving_port":"[Hamburg?]",
         "this_ship_joining_date":"1850-09",
         "year_of_birth":NumberLong(1855),
         "place_of_birth":"Liverpool",
         "additional_notes":"Discharged - in hospital",
         "signed_with_mark":"Y",
         "this_ship_capacity":"$",
         "last_ship_leaving_date":NumberLong(1877),
         "last_ship_port":"Liverpool"
      }
   ],
   "vessel name":"Jane Ellen",
   "port of registry":"Holland"
}

I am trying to retrieve only the "this_ship_capacity" values and write it into a python DataFrame for analysis but other thing keeps coming with the data I need. Here is my query:

cursor = db.data.aggregate([
        {"$match":{"mariners.this_ship_capacity":{"$exists": "true"}}},
        {"$project":{ "_id":0,
            "mariners.this_ship_capacity":{
                "$filter":{
                    "input": "$mariners.this_ship_capacity",
                    "as": "result",
                    "cond":{"$ne":[{"type":"$mariners.this_ship_capacity"}, "missing"]
                            }
                    }
                }
            }}
    ])

Here is how the output looks:

{u'mariners': [{u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']},
               {u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']},
               {u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']},
               {u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']}]}
{u'mariners': [{u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']},
               {u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']},
               {u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']},
               {u'this_ship_capacity': [u'Mate', u'$', u'Master', u'Mate']}]}

I just need the values Master, Mate etc and not the other things coming with it.

mongodb python
2 Answers

give this pipeline a shot. if that's not the expected result, let me know the exact shape of the output you need.

db.data.aggregate(
    [
        {
            $match: {
                "mariners.this_ship_capacity": { $exists: true }
            }
        },
        {
            $set: {
                mariners: {
                    $filter: {
                        input: "$mariners",
                        as: "m",
                        cond: { $ne: ["$$m.this_ship_capacity", "missing"] }
                    }
                }
            }
        },
        {
            $project: {
                _id: 0,
                mariners: {
                    $map: {
                        input: "$mariners",
                        as: "m",
                        in: { this_ship_capacity: "$$m.this_ship_capacity" }
                    }
                }
            }
        }
    ]
)
user_43684850

Demo - https://mongoplayground.net/p/JMIviVTeAJU

db.collection.aggregate([
  { $unwind: "$mariners" }, // break into individual documents
  { $match: {
      $and: [ // filter
       { "mariners.this_ship_capacity": { $exists: true } },
       { "mariners.this_ship_capacity": {  $ne: "missing" } }
      ]
    }
  },
  { $project: { _id: 0, "this_ship_capacity": "$mariners.this_ship_capacity" } }
])

Output

[
  {
    "this_ship_capacity": "Mate"
  },
  {
    "this_ship_capacity": "$"
  }
]

Use group if you want to output as a single array

Demo - https://mongoplayground.net/p/Am6O6XyIOyi

{
  $group: { _id: null, "this_ship_capacity": { $push: "$mariners.this_ship_capacity" } }
}

Output -

[
  {
    "_id": null,
    "this_ship_capacity": [
      "Mate",
      "$"
    ]
  }
]
user_22242650
Related