Avoid empty array elements in mongo db

Question

How to avoid empty array while filtering results while querying a collection in MongoDb

[
  {
    "_id": ObjectId("5d429786bd7b5f4ae4a64790"),
    "extensions": {
      "outcome": "success",
      "docType": "ABC",
      "Roll No": "1"
    },
    "data": [
      {
        "Page1": [
          {
            "heading": "LIST",
            "content": [
              {
                "text": "<b>12345</b>"
              },

            ],

          }
        ],
        "highlights": [
          {
            "name": "ABCD",
            "text": "EFGH",

          }
        ],
        "marks": [
          {
            "revision": "revision 1",
            "Score": [
              {
                "maths": "100",
                "science": "40",
                "history": "90"
              },
              {
                "lab1": "25",
                "lab2": "25"
              }
            ],
            "Result": "Pass"
          },
          {
            "revision": "revision 1",
            "Score": [
              {
                "maths": "100",
                "science": "40"
              },
              {
                "lab1": "25",
                "lab2": "25"
              }
            ],
            "Result": "Pass"
          }
        ]
      }
    ]
  }
]

I am looking for results that has only "history" marks in the score array.

I tried the following query (in mongo 3.6.10) but it returns empty score array as well the array that has history as well

db.getCollection('student_scores').find({
  "data.marks.score.history": {
    $not: {
      $type: 10
    },
    $exists: true
  }
},
{
  "extensions.rollNo": 1,
  "data.marks.score.history": 1
})

Desired output is

{
  "extensions": {
    "rollNo": "1"
  },
  "data": [
    {
      "marks": [
        {
          "Score": [
            {
              "history": "90"
            }
          ]
        }
      ]
    }
  ]
}

Recommend you show a desired output doc from the query; the nuances of "only history marks in the score array" can change the nature of the query. Also recommend providing 2 input docs, one that matches the desired output and one that does not. And strip away Page1, extensions, etc. These are not germane to the filter you seek and it makes it harder to "copy and test" the material. — Buzz Moschetti
– Buzz Moschetti, Commented Aug 27, 2019 at 20:34
I have corrected the josn. I am encountering nested array and my scenario involves not null to filter to fetch only history attribute — user3586664
– user3586664, Commented Aug 28, 2019 at 2:57
I think the data shape is still a little ... off. I don't understand the design rationale of the Score array of objects. You might simplify your design by having Score being a simple object, e.g. Score: { "maths":100, "science":40, "lab1":25 } And also, those scores should numbers (ints) not strings... — Buzz Moschetti
– Buzz Moschetti, Commented Aug 28, 2019 at 16:29
Can you please check my answer? It ought to help with your requirement and simplify your result! — bvrakvs
– bvrakvs, Commented Sep 30, 2019 at 4:32

bvrakvs · Accepted Answer · 2019-08-29 00:40:13Z

2

I used something like the following;

db.getCollection('student_scores').aggregate([
  {
    $unwind: "$data"
  },
  {
    $unwind: "$data.marks"
  },
  {
    $unwind: "$data.marks.Score"
  },
  {
    $match: {
      "data.marks.Score.history": {
        $exists: true,
        $not: {
          $type: 10
        }
      }
    }
  },
  {
    $project: {
      "extensions.Roll No": 1,
      "data.marks.Score.history": 1
    }
  },
  {
    $group: {
      _id: "$extensions.Roll No",
      history_grades: {
        $push: "$data.marks.Score.history"
      }
    }
  }
])

where I got the following result with your input (I think more readable than your expected output);

[
  {
    "_id": "1",
    "history_grades": [
      "90"
    ]
  }
]

where _id represents "extensions.Roll No" value for any given data set.

What do you think?

check with a bigger input on mongoplayground

edited Aug 29, 2019 at 0:40

answered Aug 28, 2019 at 4:41

bvrakvs

14.7k4 gold badges55 silver badges96 bronze badges

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

2 Comments

Buzz Moschetti Over a year ago

Not sure this tests for only history...? Pretty sure this logic tests for at least one history, not only history. But I am not 100% certain of the requirements from the OP.

bvrakvs Over a year ago

@BuzzMoschetti OP means for the final output when he says he wants only history, for the output has two Score arrays, one with the expected history result and another empty object, and another with two empty objects. Check this

Buzz Moschetti · Accepted Answer · 2019-08-28 20:07:33Z

OK, so I still think the data design here with the Score array is a little off but here is solution that will ensure that a Score array contains only 1 entry and that entry is for a key of history. We use dotpath array diving as a trick to get to the value of history.

c = db.foo.aggregate([
{$unwind: "$data"}
,{$unwind: "$data.marks"}

,{$project: {
result: {$cond: [
    {$and: [ // if
        {$eq: [1, {$size: "$data.marks.Score"}]}, // Only 1 item...

        //  A little trick!  $data.marks.Score.history will resolve to an *array*
        //  of the values associated with each object in $data.marks.Score (the parent
        //  array) having a key of history. BUT: As it resolves, if there is no 
        // field for that key, nothing is added to resolution vector -- not even a null.
        //  This means the resolved array could
        //  be **shorter** than the input.  FOr example:
        //    > db.foo.insert({"x":[ {b:2}, {a:3,b:4}, {b:7}, {a:99} ]});
        //    WriteResult({ "nInserted" : 1 })
        //    > db.foo.aggregate([ {$project: {z: "$x.b", n: {$size: "$x.b"}} } ]);
        //    { "z" : [ 2, 4, 7 ], "n" : 3 }
        //    > db.foo.aggregate([ {$project: {z: "$x.a", n: {$size: "$x.a"}} } ]);
        //    { "z" : [ 3, 99 ], "n" : 2 }
        //
        //  You must be careful about this.
        //  But we also know this resolved vector is of size 1 (see above) so we can go ahead and grab
        //  the 0th item and that becomes our output. 
        //  Note that if we did not have the requirement of ONLY history, then we would not
        //  need the fancy $cond thing. 
        {$arrayElemAt: ["$data.marks.Score.history",0]} 
             ]},
    {$arrayElemAt: ["$data.marks.Score.history",0]},  // then (use value of history)
    null ] }   // else set null

,extensions: "$extensions"  // just carry over extensions
    }}

,{$match: {"result": {$ne: null} }} // only take good ones.

Collectives™ on Stack Overflow

Avoid empty array elements in mongo db

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related