Querying a multi-nested array in MongoDb 3.4.2

user_3959543 0
Viewed 68

MongoDB Version - 3.4.2

I'm trying to query within the Sitecore Analytics database, trying to retrieve all users that are associated with a given List Id.

The example dataset I have follows the default Sitecore Analytics setup:

"Tags" : {
    "Entries" : {
        "ContactLists" : {
            "Values" : {
                "0" : {
                    "Value" : "{1E2D1AB7-72A0-4FF7-906B-DCDC020B87D2}",
                    "DateTime" : ISODate("2020-10-23T17:38:13.891Z")
                },
                "1" : {
                    "Value" : "{28BECCD3-476B-4B1D-9A75-02E59EF21286}",
                    "DateTime" : ISODate("2018-04-18T14:22:41.763Z")
                },
                "2" : {
                    "Value" : "{2C2BB0C3-483D-490E-B93A-9155BFBBE5DC}",
                    "DateTime" : ISODate("2018-05-10T14:26:08.494Z")
                },
                "3" : {
                    "Value" : "{DBE480F6-E305-4B35-9E6D-CBED64F4E44F}",
                    "DateTime" : ISODate("2018-10-27T02:41:28.776Z")
                },
            }
        }
    }
},

I want to iterate through all the entries within Values without having to specify 0/1/2/3, avoiding the following:

db.getCollection('Contacts').find({"Tags.Entries.ContactLists.Values.1.Value": "{28BECCD3-476B-4B1D-9A75-02E59EF21286}"})

I've tried the following:

db.getCollection('Contacts').find({"Tags.Entries.ContactLists.Values": {$elemMatch : {"Value":"{28BECCD3-476B-4B1D-9A75-02E59EF21286}"}}})    

db.getCollection('Contacts').find({'Tags' : {$elemMatch : {$all : ['{28BECCD3-476B-4B1D-9A75-02E59EF21286}']}}})

db.getCollection('Contacts').forEach(function (doc) {
       for(var i in doc.Tags.Entries.ContactLists.Values)
       {
          doc.Tags.Entries.ContactLists.Values[i].Value = "{28BECCD3-476B-4B1D-9A75-02E59EF21286}";
       }       
    })

And a few other variations which I cannot recall now. And none work.

Any ideas if this is possible or on how to do this?

I want the outcome to just show filter out the results showing only the entries containing the matching GUID

Many thanks!

sitecore aggregation-framework mongodb-query mongodb
2 Answers

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

It can be done using aggregation pipeline

db.collection.aggregate([
  {
    $addFields: {
      filteredValue: {
        $filter: {
          input: {
            $objectToArray: "$Tags.Entries.ContactLists.Values"
          },
          as: "val",
          cond: {
            $eq: [ // filter condition
              "$$val.v.Value",
              "{28BECCD3-476B-4B1D-9A75-02E59EF21286}"
            ]
          }
        }
      }
    }
  }
])

Output -

[
  {
    "Tags": {
      "Entries": {
        "ContactLists": {
          "Values": {
            "0": {
              "DateTime": ISODate("2020-10-23T17:38:13.891Z"),
              "Value": "{1E2D1AB7-72A0-4FF7-906B-DCDC020B87D2}"
            },
            "1": {
              "DateTime": ISODate("2018-04-18T14:22:41.763Z"),
              "Value": "{28BECCD3-476B-4B1D-9A75-02E59EF21286}"
            },
            "2": {
              "DateTime": ISODate("2018-05-10T14:26:08.494Z"),
              "Value": "{2C2BB0C3-483D-490E-B93A-9155BFBBE5DC}"
            },
            "3": {
              "DateTime": ISODate("2018-10-27T02:41:28.776Z"),
              "Value": "{DBE480F6-E305-4B35-9E6D-CBED64F4E44F}"
            }
          }
        }
      }
    },
    "_id": ObjectId("5a934e000102030405000000"),
    "filteredValue": [
      {
        "k": "1",
        "v": {
          "DateTime": ISODate("2018-04-18T14:22:41.763Z"),
          "Value": "{28BECCD3-476B-4B1D-9A75-02E59EF21286}"
        }
      }
    ]
  }
]
user_22242650

You can not use $elemMatch because Values is not array, but object. You can solve the problem with Aggregation Pipeline:

  • $addFields to add new field Values_Array that will be array representation of Values object.
  • $objectToArray to transform Values object to array
  • $match to find all documents that has requested value in new Values_Array field
  • $project to specify which properties to return from the result
db.getCollection('Contacts').aggregate([
  {
    "$addFields": {
      "Values_Array": {
        "$objectToArray": "$Tags.Entries.ContactLists.Values"
      }
    }
  },
  {
    "$match": {
      "Values_Array.v.Value": "{28BECCD3-476B-4B1D-9A75-02E59EF21286}"
    }
  },
  {
    "$project": {
      "Tags": 1
    }
  }
])

Here is the working example: https://mongoplayground.net/p/2gY-vu3Qrvz

user_143898300
Related