Question 115197 in Mongodb Question posted in
The official documentation can be found here.

MongoDB find query for nested object

I have the below MongoDB json document

{
"_id" : ObjectId("605c8351e874ab0012081b7d"),
"fw8_pk" : NumberLong(604063),
"customer_name" : "Testing_9.6",
"customer_type" : "NP",
"customer_status" : "A",
"created_by" : NumberLong(0),
"creation_date" : NumberLong(1616675665396),
"last_updated_by" : NumberLong(0),
"last_updated_date" : NumberLong(1681210340164),
"last_sync_ts" : NumberLong(0),
"subscription" : {
    "secondTrainingStatus" : {},
    "lastPeriodCurrent" : false,
    "modify_date" : NumberLong(1681236913526),
    "solution_list" : {
        "5eb6b989625d12ea8edac74b" : [ 
            "57aa92751f610a8995d796d5", 
            "5ec29b7e93a3c75ad8e04ad2", 
            "5f73094dfde8bd2402889783", 
            "545f95bee4b0ea26a1882362", 
            "5ec29a2f93a3c75ad8e04ad0", 
            "5ec29b3a93a3c75ad8e04ad1", 
            "545f95bee4b0ea26a1882459", 
            "5acf8f129fe8d2a3e0657496", 
            "5ec29bd793a3c75ad8e04ad3"
        ]
    },
    "time_periods" : {
        "1" : {
            "start" : NumberLong(1616673600000),
            "end" : NumberLong(1704110400000),
            "desc" : "TERM 1",
            "expired" : false,
            "inGrace" : false,
            "extended" : true,
            "original_end" : NumberLong(0)
        },
        "2" : {
            "start" : NumberLong(1704196800000),
            "end" : NumberLong(1791633600000),
            "desc" : "TERM 2",
            "expired" : false,
            "inGrace" : false,
            "extended" : false,
            "original_end" : NumberLong(0)
        }
    }
 }

now i want to find all the subscription.term who has an expired:false.
I have run the below queries but couldn’t get any result

db.collection.find({
"subscription.time_periods": {
    $elemMatch: {
        expired: false
    }
 }
})

I am not sure where the problem is. Is it because time periods has a key value pair too?

Tags:

3

Answers


  1. 0

    Use $objectToArray

    db.collection.aggregate([
  {
    $project: {
      arr: {
        $objectToArray: "$subscription.time_periods"
      }
    }
  },
  {
    $unwind: "$arr"
  },
  {
    $match: {
      "arr.v.expired": false
    }
  }
])

    mongoplayground

    Login or Signup to reply.
  2. 0

    Your solution doesn’t work because the $elemMatch operator is used to match elements of an array, and time_periods is not an array.

    You will need to use an aggregation pipeline in this case:

    db.collection.aggregate([
  // Step 1: converts object to array
  {
    $project: {
      _id: 1,
      "subscription.terms": {
        $objectToArray: "$subscription.time_periods"
      }
    }
  },
  { // Step 2: filter. (`v` is value at `subscription.time_periods.k` from step 1)
    $match: {
      "subscription.terms.v.expired": false
    }
  },
  {
    $project: {
      _id: 1,
      "subscription.terms": {
        $filter: {
          input: "$subscription.terms.v",
          as: "terms",
          cond: {
            $eq: [
              "$$terms.expired",
              false
            ]
          }
        }
      }
    }
  }
])

    Final output

    [
  {
    "_id": ObjectId("605c8351e874ab0012081b7d"),
    "subscription": {
      "terms": [
        {
          "desc": "TERM 2",
          "end": NumberLong(1791633600000),
          "expired": false,
          "extended": false,
          "inGrace": false,
          "original_end": NumberLong(0),
          "start": NumberLong(1704196800000)
        },
        {
          "desc": "TERM 1",
          "end": NumberLong(1704110400000),
          "expired": false,
          "extended": true,
          "inGrace": false,
          "original_end": NumberLong(0),
          "start": NumberLong(1616673600000)
        }
      ]
    }
  }
]
    Login or Signup to reply.
  3. 0

    I would do it like this:

    db.collection.aggregate([
   {
      $set: {
         "subscription.time_periods": { $objectToArray: "$subscription.time_periods" }
      }
   },
   {
      $set: {
         "subscription.time_periods": {
            $filter: {
               input: "$subscription.time_periods",
               cond: { $not: "$$this.v.expired" }
            }
         }
      }
   },
   {
      $set: {
         "subscription.time_periods": { $arrayToObject: "$subscription.time_periods" }
      }
   }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search