Search nested array if all conditions are satisfied

Working with Data
1

For testing purposes, I created a collection with only one document:

{
    "_id" : ObjectId("5ee7ba7ea3ac0c3192c19e58"),
    "workspace_id" : 1,
    "attributes" : {
        "first_name" : "John",
        "last_name" : "Doe",
        "email" : "john.doe@example.net",
        "phone_number" : "+1234567890",
        "gender" : "Male"
    },
    "events" : [ 
        {
            "event" : "search_results",
            "event_data" : {
                "query" : "Shirts",
                "results" : [ 
                    {
                        "name" : "Red T-Shirt XL"
                    }
                ]
            }
        }, 
        {
            "event" : "search_results",
            "event_data" : {
                "query" : "Shirts",
                "results" : [ 
                    {
                        "name" : "Blue T-Shirt XL"
                    }
                ]
            }
        }
    ],
    "created_at" : ISODate("2020-03-17T09:58:02.000Z"),
    "updated_at" : ISODate("2020-03-17T09:58:02.000Z")
}

Now, I want to query all clients who had “search_results” but whose results are those where Red T-Shirt XL and Blue T-Shirt XL appeared.

This is my query:

db.getCollection('clients').find(

{
  "$and": [
    {
      "workspace_id": 1,
      "deleted_at": {
        "$exists": false
      }
    },
    {
      "events.event": "search_results",
      "$and": [
          {"events.event_data.results.name" : "Red T-Shirt XL"},
          {"events.event_data.results.name" : "Blue T-Shirt XL"},
      ]
    }
  ]
}
)

However, it returns this ONE record, but shouldn’t… It should if Red and Blue shirts are in results array. Like this:

 {
            "event" : "search_results",
            "event_data" : {
                "query" : "Shirts",
                "results" : [ 
                    {
                        "name" : "Red T-Shirt XL"
                    },
                    {
                        "name" : "Blue T-Shirt XL"
                    }
                ]
            }
        }

Thank you!

2

Hi.
Did you try to use the array selector?

db.getCollection('clients').find(

{
  "$and": [
    {
      "workspace_id": 1,
      "deleted_at": {
        "$exists": false
      }
    },
    {
      "events.event": "search_results",
      "$and": [
          {"events.event_data.results.0.name" : "Red T-Shirt XL"},
          {"events.event_data.results.1.name" : "Blue T-Shirt XL"},
      ]
    }
  ]
}
)

Please try with this.
I’ve added the array selector to the $and query.

   "$and": [
          {"events.event_data.results.0.name" : "Red T-Shirt XL"},
          {"events.event_data.results.1.name" : "Blue T-Shirt XL"},
      ]
3

@Valentine_Soin

Many thanks for the solution. This definitely works! Not sure how I forgot that I could try something like this… :slight_smile:

Cheers!

4

@Valentine_Soin

Unfortunately, this doesn’t work because:

  "$and": [
          {"events.event_data.results.0.name" : "Blue T-Shirt XL"},
          {"events.event_data.results.1.name" : "Red T-Shirt XL"},
      ]

and this:

  "$and": [
          {"events.event_data.results.0.name" : "Red T-Shirt XL"},
          {"events.event_data.results.1.name" : "Blue T-Shirt XL"},
      ]

does not give the same results. I just switched Red & Blue words… Seems like I need to figure out something different.

5

Just a quick update.

In case we have something like this:

{
    "_id" : ObjectId("5ee7ba7ea3ac0c3192c19e58"),
    "workspace_id" : 1,
    "attributes" : {
        "first_name" : "John",
        "last_name" : "Doe",
        "email" : "john.doe@example.net",
        "phone_numbe" : "1-620-410-3432 x97756",
        "gender" : "Female"
    },
    "events" : [ 
        {
            "event" : "search_results",
            "event_data" : {
                "query" : "Shirts",
                "results" : [ 
                    {
                        "name" : "Red T-Shirt XL"
                    }
                ]
            },
            "created_at" : ISODate("2020-06-15T09:58:02.000Z")
        }, 
        {
            "event" : "search_results",
            "event_data" : {
                "query" : "Shirts",
                "results" : [ 
                    {
                        "name" : "Blue T-Shirt XL"
                    }
                ]
            },
            "created_at" : ISODate("2020-05-15T09:58:02.000Z")
        }
    ],
    "created_at" : ISODate("2020-03-17T09:58:02.000Z"),
    "updated_at" : ISODate("2020-03-17T09:58:02.000Z")
}

and execute the following query:

db.getCollection('clients').find(
{
  "$and": [
    {
      "workspace_id": 1,
      "deleted_at": {
        "$exists": false
      }
    },
    {
      "events.event": "search_results",
      "events.created_at" : { $gte : new ISODate("2020-01-01T20:15:31Z")},
      "$and": [
          {"events.event_data.results.name" : "Red T-Shirt XL"},
          {"events.event_data.results.name" : "Blue T-Shirt XL"},
      ]
    }
  ]
}
)

This should return nothing because as you can see one event is created in May and one in June and I would like to have only those who searched for Red and Blue T-Shirt after June 1st.

Not sure what I am missing here. When using the query above, it returns one record.

EDIT:

Seems like I got something:

db.getCollection('clients').find(
{
  "$and": [
    {
      "workspace_id": 1,
      "deleted_at": {
        "$exists": false
      }
    },
    {
      "events.event": "search_results",
      "events" : {
          "$elemMatch" : {
            "created_at" : { $gte : new ISODate("2020-06-01T20:15:31Z")},
            "$and": [
               {"event_data.results.name" : "Red T-Shirt XL"},
               {"event_data.results.name" : "Blue T-Shirt XL"},
            ]
          }   
       }
    }
  ]
}
)