Nested Queries
Nested queries are a powerful feature of OpenSearch that allows you to search within nested objects or arrays. This feature is especially useful when working with complex data structures that require deep search capabilities.
Where Nested Object
This method allows you to query nested objects within an OpenSearch document.
Nested fields are useful when you need to store large arrays of objects without flattening them into a single array or overindexing the parent index.
BlogPost::whereNestedObject('comments', function (Builder $query) { $query->where('comments.country', 'Peru')->where('comments.likes', 5);})->get();This will return all the blog posts where the comments field contains an object with the country field set to ‘Peru’ and the likes field set to 5.
{ "index": "blog_posts", "body": { "query": { "nested": { "path": "comments", "query": { "bool": { "must": [ { "term": { "comments.country.keyword": { "value": "Peru" } } }, { "term": { "comments.likes": { "value": 5 } } } ] } } } }, "size": 1000 }}Where Nested Object with Filtered Inner Hits
- Option 1:
MyModel::whereNestedObject($field, $closureQuery, true)
- Option 2:
MyModel::filterNested($field, $closureQuery)
Example:
// Query nested and filter inner hitsBlogPost::whereNestedObject('comments', function (Builder $query) { $query->where('comments.country', 'Peru') ->where('comments.likes', 5) ->orderBy('likes', 'desc') ->limit(5);},true)->get();{ "index": "blog_posts", "body": { "query": { "nested": { "path": "comments", "query": { "bool": { "must": [ { "term": { "comments.country.keyword": { "value": "Peru" } } }, { "term": { "comments.likes": { "value": 5 } } } ] } }, "inner_hits": { "sort": [ { "comments.likes": { "order": "desc" } } ], "size": 5 } } }, "size": 1000 }}Note on Nested limits
The default max limit for OpenSearch on nested values when using inner_hits is 100. Trying to set a limit higher than that will result in an error.
You can change the limit by setting the max_inner_result_window value in your OpenSearch configuration.
Schema::create('blog_posts', function (IndexBlueprint $index) { $index->nested('comments'); $index->settings('max_inner_result_window', 200);});Then you can:
BlogPost::where('status', 5)->whereNestedObject('comments', function ($query) { $query->orderByDesc('likes')->limit(200);},true)->orderBy('created_at')->first();{ "index": "blog_posts", "body": { "query": { "bool": { "must": [ { "term": { "status": { "value": 5 } } }, { "nested": { "path": "comments", "query": { "exists": { "field": "comments" } }, "inner_hits": { "sort": [ { "comments.likes": { "order": "desc" } } ], "size": 200 } } } ] } }, "sort": [ { "created_at": { "order": "asc" } } ], "size": 1000 }}Where Not Nested Object
Similar to whereNestedObject, this method allows you to query nested objects within an OpenSearch document.
However, it excludes documents that match the specified nested object query.
BlogPost::whereNotNestedObject('comments', function (Builder $query) { $query->where('comments.country', 'Peru');})->get();This will return all the blog posts where there are no comments from Peru
{ "index": "blog_posts", "body": { "query": { "bool": { "must_not": [ { "nested": { "path": "comments", "query": { "term": { "comments.country.keyword": { "value": "Peru" } } }, } } ] } }, "size": 1000 }}Where Not Nested Object with Filtered Inner Hits
Follow up the whereNotNestedObject clause with a whereNestedObject set to true true and an inner query for the desired filter
BlogPost::whereNotNestedObject('comments', function (Builder $query) { $query->where('country', 'Peru');})->whereNestedObject('comments', function (Builder $query) { $query->orderBy('likes', 'desc')->limit(5);},true)->get();{ "index": "blog_posts", "body": { "query": { "bool": { "must_not": [ { "nested": { "path": "comments", "query": { "term": { "comments.country.keyword": { "value": "Peru" } } } } } ], "must": [ { "nested": { "path": "comments", "query": { "exists": { "field": "comments" } }, "inner_hits": { "sort": [ { "comments.likes": { "order": "desc" } } ], "size": 5 } } } ] } }, "size": 1000 }}Order By Nested Field
This method allows you to order the results of a query by a nested field.
This is useful when you need to sort the results of a query based on a nested field.
Parameters scope: orderByNested($field, $direction = 'asc', $mode = 'avg')
BlogPost::where('status', 5)->orderByNested('comments.likes', 'desc', 'sum')->limit(5)->get();This will return all the blog posts where the status is 5, ordered by the sum of the likes of the comments in descending order.
{ "index": "blog_posts", "body": { "query": { "term": { "status": { "value": 5 } } }, "sort": [ { "comments.likes": { "mode": "avg", "nested": { "path": "comments" }, "order": "asc" } } ], "size": 5 }}Filtered Inner Hits Examples
1. No Filtering:
BlogPost::where('status', 5)->orderBy('created_at')->first();{ "index": "blog_posts", "body": { "query": { "term": { "status": { "value": 5 } } }, "sort": [ { "created_at": { "order": "asc" } } ], "size": 1000 }}return example:
{ "_id": "1IiLKG38BCOXW3U9a4zcn", "title": "My first post", "status": 5, "comments": [ { "name": "Damaris Ondricka", "comment": "Quia quis facere cupiditate unde natus dolorem. Quia voluptatem in nam occaecati. Veritatis libero neque vitae.", "country": "Peru", "likes": 6 }, { "name": "Cole Beahan", "comment": "Officia ut dolorem itaque sapiente repellendus consequatur. Voluptas veniam quis eligendi. Aliquid voluptatem reiciendis ut.", "country": "Sweden", "likes": 0 }, { "name": "April Von", "comment": "Repudiandae rem aspernatur neque molestiae voluptatibus ut aut. Animi dolor id voluptas. Blanditiis a est nobis voluptatem sed sed illum esse.", "country": "Switzerland", "likes": 10 }, { "name": "Ella Ruecker", "comment": "Et deleniti ab cumque nobis ut ullam. Exercitationem qui sequi voluptatem delectus sunt nobis. Vel libero nihil quas inventore omnis. Harum corrupti consequatur quibusdam ut.", "country": "UK", "likes": 5 }, { "name": "Mabelle Schinner", "comment": "Aliquid molestiae quas vitae ipsam neque nam sed. Facere blanditiis repellendus sequi autem. Explicabo cupiditate porro quia animi ut minus tempora ut.", "country": "Switzerland", "likes": 7 } ]}2. Filter comments from Switzerland:
BlogPost::where('status', 5)->whereNestedObject('comments', function ($query) { $query->where('country', 'Switzerland'); //or comments.country},true)->orderBy('created_at')->first();{ "index": "blog_posts", "body": { "query": { "bool": { "must": [ { "term": { "status": { "value": 5 } } }, { "nested": { "path": "comments", "query": { "term": { "comments.country.keyword": { "value": "Switzerland" } } }, "inner_hits": {} } } ] } }, "sort": [ { "created_at": { "order": "asc" } } ], "size": 1000 }}returns:
{ "_id": "1IiLKG38BCOXW3U9a4zcn", "title": "My first post", "status": 5, "comments": [ { "name": "April Von", "comment": "Repudiandae rem aspernatur neque molestiae voluptatibus ut aut. Animi dolor id voluptas. Blanditiis a est nobis voluptatem sed sed illum esse.", "country": "Switzerland", "likes": 10 }, { "name": "Mabelle Schinner", "comment": "Aliquid molestiae quas vitae ipsam neque nam sed. Facere blanditiis repellendus sequi autem. Explicabo cupiditate porro quia animi ut minus tempora ut.", "country": "Switzerland", "likes": 7 } ]}3. Filter comments from Switzerland ordered by likes:
BlogPost::where('status', 5)->whereNestedObject('comments', function ($query) { $query->where('country', 'Switzerland')->orderBy('likes');},true)->orderBy('created_at')->first();{ "index": "blog_posts", "body": { "query": { "bool": { "must": [ { "term": { "status": { "value": 5 } } }, { "nested": { "path": "comments", "query": { "term": { "comments.country.keyword": { "value": "Switzerland" } } }, "inner_hits": { "sort": [ { "comments.likes": { "order": "asc" } } ] } } } ] } }, "sort": [ { "created_at": { "order": "asc" } } ], "size": 1000 }}returns:
{ "_id": "1IiLKG38BCOXW3U9a4zcn", "title": "My first post", "status": 5, "comments": [ { "name": "Mabelle Schinner", "comment": "Aliquid molestiae quas vitae ipsam neque nam sed. Facere blanditiis repellendus sequi autem. Explicabo cupiditate porro quia animi ut minus tempora ut.", "country": "Switzerland", "likes": 7 }, { "name": "April Von", "comment": "Repudiandae rem aspernatur neque molestiae voluptatibus ut aut. Animi dolor id voluptas. Blanditiis a est nobis voluptatem sed sed illum esse.", "country": "Switzerland", "likes": 10 } ]}4. Filter comments with likes greater than 5:
BlogPost::where('status', 5)->whereNestedObject('comments', function ($query) { $query->where('likes', '>', 5);},true)->orderBy('created_at')->first();{ "index": "blog_posts", "body": { "query": { "bool": { "must": [ { "term": { "status": { "value": 5 } } }, { "nested": { "path": "comments", "query": { "range": { "comments.likes": { "gt": 5 } } }, "inner_hits": {} } } ] } }, "sort": [ { "created_at": { "order": "asc" } } ], "size": 1000 }}returns:
{ "_id": "1IiLKG38BCOXW3U9a4zcn", "title": "My first post", "status": 5, "comments": [ { "name": "Damaris Ondricka", "comment": "Quia quis facere cupiditate unde natus dolorem. Quia voluptatem in nam occaecati. Veritatis libero neque vitae.", "country": "Peru", "likes": 6 }, { "name": "April Von", "comment": "Repudiandae rem aspernatur neque molestiae voluptatibus ut aut. Animi dolor id voluptas. Blanditiis a est nobis voluptatem sed sed illum esse.", "country": "Switzerland", "likes": 10 }, { "name": "Mabelle Schinner", "comment": "Aliquid molestiae quas vitae ipsam neque nam sed. Facere blanditiis repellendus sequi autem. Explicabo cupiditate porro quia animi ut minus tempora ut.", "country": "Switzerland", "likes": 7 } ]}4. Filter comments with likes greater than or equal to 5, limit 2:
BlogPost::where('status', 5)->whereNestedObject('comments', function ($query) { $query->where('likes', '>=', 5)->limit(2);},true)->orderBy('created_at')->first();{ "index": "blog_posts", "body": { "query": { "bool": { "must": [ { "term": { "status": { "value": 5 } } }, { "nested": { "path": "comments", "query": { "range": { "comments.likes": { "gte": 5 } } }, "inner_hits": { "size": 2 } } } ] } }, "sort": [ { "created_at": { "order": "asc" } } ], "size": 1000 }}returns:
{ "_id": "1IiLKG38BCOXW3U9a4zcn", "title": "My first post", "status": 5, "comments": [ { "name": "Damaris Ondricka", "comment": "Quia quis facere cupiditate unde natus dolorem. Quia voluptatem in nam occaecati. Veritatis libero neque vitae.", "country": "Peru", "likes": 6 }, { "name": "April Von", "comment": "Repudiandae rem aspernatur neque molestiae voluptatibus ut aut. Animi dolor id voluptas. Blanditiis a est nobis voluptatem sed sed illum esse.", "country": "Switzerland", "likes": 10 } ]}