How to return results only where two properties of a nested object match

[Max_MacMillan]

Hello! I’m new to Atlas search and trying to wrap my head around compound operators. I have a collection with documents like this:

{
  "itemType": "fruit",
  "name": "nectarines",
  "displayName": "Nectarines",
  "zones": [
    {
      "zone": "10",
      "dates": {
        "plant": [ "January", "February"],
        "harvest": ["June", "July", "August"]
      }
    },
    {
      "zone": "11",
      "dates": {
        "plant": ["December", "January"],
        "harvest": ["May","June","July"]
      }
    },
    {
      "zone": "12",
      "dates": {
        "plant": ["November", "December"],
        "harvest": ["April", "May","June"]
      }
    },
    {
      "zone": "13",
      "dates": {
        "plant": ["October", "November"],
        "harvest": [ "March","April", "May"]
      }
    }
  ]
}
{
  "itemType": "fruit",
  "name": "oranges",
  "displayName": "Oranges",
  "zones": [
    {
      "zone": "10",
      "dates": {
        "plant": ["February","March","April"],
        "harvest": ["November","December", "January", "February", "March", "April"]
      }
    },
    {
      "zone": "11",
      "dates": {
        "plant": ["January","February","March"],
        "harvest": ["October","November", "December", "January", "February", "March"]
      }
    },
    {
      "zone": "12",
      "dates": {
        "plant": ["December", "January", "February"],
        "harvest": ["September", "October", "November", "December", "January", "February"]
      }
    },
    {
      "zone": "13",
      "dates": {
        "plant": ["November", "December", "January"],
        "harvest": ["August", "September", "October", "November", "December", "January"]
      }
    }
  ]
}

And then in my client I have a search form with two fields - one for “zone” and one for “month”. I want to return query results based on the zones data for each document - for example, if I searched for all items which are harvested in July in Zone 11, nectarines would be returned but not oranges.

I indexed this collection like this:

{
  "mappings": {
    "dynamic": true,
    "fields": {
      "zones": {
        "dynamic": true,
        "type": "embeddedDocuments"
      }
    }
  }
}

I have been struggling with compound, must, and filter but so far I’ve only been able to return documents which include the queried zone and harvest month in ANY values in the zones array, not specifically only those where the zone and harvest month are property values in the same object.

Here’s what I thought would work, but doesn’t:

[
  {
    $search: {
      index: "searchMarketItems",
      embeddedDocument: {
        path: "zones",
        operator: {
          compound: {
            filter: [
              {
                text: {
                  path: "zones.zone",
                  query: "11",
                },
              },
            ],
            must: [
              {
                text: {
                  path: "zones.dates.harvest",
                  query: "july",
                },
              },
            ],
          },
        },
      },
    },
  },
];

Any help would be appreciated!

[Max_MacMillan]

I was able to create an aggregation pipeline that works, (stages of $unwind, then $match, then $unwind, then $match) but I’d still like to know if this is possible using $search?

[Jason_Tran]

Hi @Max_MacMillan,

I assume since you got it working via your above mentioned stages, you have a desired output already. Could you provide that output here just so that I can verify if it is possible with $search?

In the meantime, with the 2 sample documents you provided in my test environment, I had the following index definition:

{
  "mappings": {
    "dynamic": false,
    "fields": {
      "zones": {
        "fields": {
          "dates": {
            "fields": {
              "harvest": {
                "type": "string"
              }
            },
            "type": "document"
          },
          "zone": {
            "type": "string"
          }
        },
        "type": "embeddedDocuments"
      }
    }
  }
}

I then performed the following $search (my test index called default):

db.collection.aggregate({
  $search: {
    index: 'default',
    embeddedDocument: {
      path: 'zones',
      operator: {
        compound: {
          filter: [
            {
              text: {
                path: 'zones.zone',
                query: '11'
              }
            }
          ],
          must: [
            {
              text: {
                path: 'zones.dates.harvest',
                query: 'July'
              }
            }
          ]
        }
      }
    }
  }
})

Which gave the following output:

[
  {
    _id: ObjectId("64ab6ba0220ed429db5aa12d"),
    itemType: 'fruit',
    name: 'nectarines',
    displayName: 'Nectarines',
    zones: [
      {
        zone: '10',
        dates: {
          plant: [ 'January', 'February' ],
          harvest: [ 'June', 'July', 'August' ]
        }
      },
      {
        zone: '11',
        dates: {
          plant: [ 'December', 'January' ],
          harvest: [ 'May', 'June', 'July' ]
        }
      },
      {
        zone: '12',
        dates: {
          plant: [ 'November', 'December' ],
          harvest: [ 'April', 'May', 'June' ]
        }
      },
      {
        zone: '13',
        dates: {
          plant: [ 'October', 'November' ],
          harvest: [ 'March', 'April', 'May' ]
        }
      }
    ]
  }
]

Regards,
Jason

[Max_MacMillan]

Hi @Jason_Tran Thanks so much for this - yes, that search solution works and the field mapping is the key I was missing. I thought that dynamic mapping everything would “just work” but if I update my index with your solution I get the same results - ‘nectarines’ and not ‘oranges’. Much appreciated!

[Jason_Tran]

Nice one - Thanks for marking the solution + updating the post as well!