How to use Atlas Search Compound "Must" aggregation to seach Root Document fields AND EmbeddedDocument fields

ONeil_Tomlinson1 · 2024-10-27T16:46:45.278Z

I have the below document

{
    "_id": "101",
    "PublisherName": "Big book publishers",
	"PublisherAddress": "123 Mian Street",
    "Books": [
        {
            "_id": "6791913210",
            "Title": "Piano made simple",
            "Author": "John Bent"
        },
        {
            "_id": "6638200210",
            "Title": "Guitar made simple",
            "Author": "Kim Larry"
        }
    ]
}

I would like to perform a Compound “MUST” search across the root fields (PublisherName,PublisherAddress) and embeded document fields (Title, Author) at the same time.

Using the below Compound “Must” aggregation on the root document fields work OK. This e aggregation will return the sample document above

{
	"$search": {
		"index": "my-book-Index",
		"compound": {
			"must": [
				{
					"text": {
						"query": "Big",
						"path": [
							"PublisherName",
							"PublisherAddress"
						]
					}
				},
				{
					"text": {
						"query": "Street",
						"path": [
							"PublisherName",
							"PublisherAddress"
						]
					}
				}
			]
		}
	}
}

But the Compound “Must” aggregation is faling to find a document when I add the EmbeddedDocument to the aggregation.

{
	"$search": {
		"index": "my-book-Index",
		"compound": {
			"must": [
				{
					"text": {
						"query": "Big",
						"path": [
							"PublisherName",
							"PublisherAddress"
						]
					}
				},
				{
					"text": {
						"query": "Piano",
						"path": [
							"PublisherName",
							"PublisherAddress"
						]
					}
				},
				{
					"embeddedDocument": {
						"operator": {
							"compound": {
								"must": [
									{
										"text": {
											"query": "Big",
											"path": [
												"Books.Title",
												"Books.Author"
											]
										}
									},
									{
										"text": {
											"query": "Piano",
											"path": [
												"Books.Title",
												"Books.Author"
											]
										}
									}
								]
							}
						},
						"path": "Books"
					}
				}
			]
		}
	}
}

How should the aggregation be formulated to return all documents that MUST have “Big” and “Piano”? “Big” is in the “PublisherName” field, and “Piano” is in the “Books.Title” field. Both MUST exist for the document to be returned.

Erik_Hatcher · 2024-10-28T10:39:50.655Z

What is your index configuration definition?

Could you create a Search Playground that demonstrates your challenge?

ONeil_Tomlinson1 · 2024-10-29T12:59:00.273Z

Here is my playground setup

search-playground.mongodb.com

Code Playground

I would like to search for “Big” and “Piano” across all searchable fields. The result should return document with _id “101” as “Big” exist in “PublisherName” and “Piano” exist in “Books.Title”.

Note: The model and search was simplified for brevity

ONeil_Tomlinson1 · 2024-10-31T16:41:12.004Z

Any thoughts on this problem I’m facing? Ive provided the playground with sample data

Erik_Hatcher · 2024-11-05T14:48:10.291Z

apologies - I responded on your other thread: Atlas Search across Enbedded Document and Root Document fields using Compound Must