nosql - Trying to fetch data from Nested MongoDB Database?

Question

I am beginner in MongoDB and struck at a place I am trying to fetch data from nested array but is it taking so long time as data is around 50K data, also it is not much accurate data, below is schema structure please see once -

    {
    "_id": {
      "$oid": "6001df3312ac8b33c9d26b86"
    },
    "City": "Los Angeles",
    "State":"California",
    "Details": [
      {
        "Name": "Shawn",
        "age": "55",
        "Gender": "Male", 
        "profession": " A science teacher with STEM",
        "inDate": "2021-01-15 23:12:17",
        "Cars": [
          "BMW","Ford","Opel"
        ],
        "language": "English"
      },
      {
        "Name": "Nicole",
        "age": "21",
        "Gender": "Female", 
        "profession": "Law student",
        "inDate": "2021-01-16 13:45:00",
        "Cars": [
          "Opel"
        ],
        "language": "English"
      }
    ],
    "date": "2021-01-16"
  } 

Here I am trying to filter date with date and Details.Cars like

db.getCollection('news').find({"Details.Cars":"BMW","date":"2021-01-16"}

it is returning details of other persons too which do not have cars- BMW , Only trying to display details of person like - Shawn which have BMW or special array value and date too not - Nicole, rest should not appear but is it not happening.

Any help is appreciated. :)

question from:https://stackoverflow.com/questions/65901803/trying-to-fetch-data-from-nested-mongodb-database

Answer 1

A combination of $match on the top-level fields and $filter on the array elements will do what you seek.

db.foo.aggregate([
    {$match: {"date":"2021-01-16"}}
    ,{$addFields: {"Details": {$filter: {
        input: "$Details",
        as: "zz",
        cond: { $in: ['BMW','$$zz.Cars'] }
        }}
    }}
    ,{$match: {$expr: { $gt:[{$size:"$Details"},0] } }}
]);

Notes:

1. $unwind is overly expensive for what is needed here and it likely means "reassembling" the data shape later.
2. We use $addFields where the new field to add (Details) already exists. This effectively means "overwrite in place" and is a common idiom when filtering an array.
3. The second $match will eliminate docs where the date matches but not a single entry in Details.Cars is a BMW i.e. the array has been filtered down to zero length. Sometimes you want to know this info so if this is the case, do not add the final $match.
4. I recommend you look into using real dates i.e. ISODate instead of strings so that you can easily take advantage of MongoDB date math and date formatting functions.