MongoDB find query for nested object

Question

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?

You got it right. It's because of time_periods has a key-value pair, and $elemMatch is an array operator. — Charchit Kapoor
– Charchit Kapoor, Commented Apr 14, 2023 at 5:03
Is time_periods really an object or is it just the way how your editor displays it? I would suggest to use an array. — Wernfried Domscheit
– Wernfried Domscheit, Commented Apr 14, 2023 at 6:47

YuTing · Accepted Answer · 2023-04-14 05:28:15Z

1

Use $objectToArray

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

mongoplayground

answered Apr 14, 2023 at 5:28

YuTing

6,6692 gold badges8 silver badges19 bronze badges

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

Comments

Wernfried Domscheit · Accepted Answer · 2023-04-14 06:58:44Z

1

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

answered Apr 14, 2023 at 6:58

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

2 Comments

user7247147 Over a year ago

I think $eq: ["$$this.v.expired", false]" is better than $not for the condition because MongoDB is schema-less

Wernfried Domscheit Over a year ago

@devdev_dev in principle yes, but I think it is more a matter of taste.

user7247147 · Accepted Answer · 2023-04-14 10:50:19Z

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)
        }
      ]
    }
  }
]

Collectives™ on Stack Overflow

MongoDB find query for nested object

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related