# Elastic Search - All Parents and Matching Nested

**URL:** <https://discuss.elastic.co/t/elastic-search-all-parents-and-matching-nested/209088>\
**Category:** Elasticsearch\
**Created:** [November 22, 2019, 4:23pm UTC](https://discuss.elastic.co/t/elastic-search-all-parents-and-matching-nested/209088 "2019-11-22T16:23:43Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ci\_ms](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@ci\_ms](https://discuss.elastic.co/u/ci_ms)\
**Post date:** [November 22, 2019, 4:23pm UTC](https://discuss.elastic.co/t/elastic-search-all-parents-and-matching-nested/209088/1 "2019-11-22T16:23:44Z")

</div>

Our use case is having a list of products and in a nested field have the last purchase date by customer number. When a customer searches for products we want to return all items that match their search term. The inner\_hits should only include the record for that customer, if there is a record at all. So if a customer searches for corn they would get 4 results potentially but only 2 of them they may have purchased before. So 2 documents would have no inner hits, and the other 2 would have inner hits of 1 document only, that customers last purchase.

Most items will have hundreds of customer purchases loaded into the nested field so we do not want to return all of those records when we only care about the last purchase date of the customer that is searching.

The current query we are testing with

```
{
    "_source": {
        "includes": ["*"],
        "excludes": ["c_purchases"]
    },
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "i_description": "corn"
                    }
                },
                {
                    "nested" : {
                        "path" : "c_purchases",
                        "inner_hits": {
                            "_source": ["*"]
                        },
                        "query" : {
                            "match": {
                                "c_purchases.customernumber": "1111"
                            }
                        }
                    }   
                }
            ]
        }
    }
}

```

The issue with the above query is it only returns items the customer bought. So in the example it would only return the 2 with a nested record in c\_purchases vs all 4 corn items.

Current Results

```
{
    "hits": {
        "total": {
            "value": 2
        },
        "hits": [
            {
                "_source": {
                    "i_description": "corn 1",
                    "i_code": "111111"
                },
                "inner_hits": {
                    "c_purchases": {
                        "hits": {
                            "total": {
                                "value": 1,
                            },
                            "hits": [
                                {
                                    "_source": {
                                        "customernumber": "100",
                                        "lastordered": "2018-01-30T00:00:00",
                                    }
                                }
                            ]
                        }
                    }
                }
            },
            {
                "_source": {
                    "i_description": "corn 2",
                    "i_code": "222222"
                },
                "inner_hits": {
                    "c_purchases": {
                        "hits": {
                            "total": {
                                "value": 1,
                            },
                            "hits": [
                                {
                                    "_source": {
                                        "customernumber": "100",
                                        "lastordered": "2018-01-30T00:00:00",
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        ]
    }
}

```

Expected Results

```
{
    "hits": {
        "total": {
            "value": 2
        },
        "hits": [
            {
                "_source": {
                    "i_description": "corn 1",
                    "i_code": "111111"
                },
                "inner_hits": {
                    "c_purchases": {
                        "hits": {
                            "total": {
                                "value": 1,
                            },
                            "hits": [
                                {
                                    "_source": {
                                        "customernumber": "100",
                                        "lastordered": "2018-01-30T00:00:00",
                                    }
                                }
                            ]
                        }
                    }
                }
            },
            {
                "_source": {
                    "i_description": "corn 2",
                    "i_code": "222222"
                },
                "inner_hits": {
                    "c_purchases": {
                        "hits": {
                            "total": {
                                "value": 1,
                            },
                            "hits": [
                                {
                                    "_source": {
                                        "customernumber": "100",
                                        "lastordered": "2018-01-30T00:00:00",
                                    }
                                }
                            ]
                        }
                    }
                }
            },
            {
                "_source": {
                    "i_description": "corn 3",
                    "i_code": "333333"
                }
            },
            {
                "_source": {
                    "i_description": "corn 4",
                    "i_code": "444444"
                }
            }
        ]
    }
}

```

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![ci\_ms](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@ci\_ms](https://discuss.elastic.co/u/ci_ms)\
**Post date:** [December 2, 2019, 3:47pm UTC](https://discuss.elastic.co/t/elastic-search-all-parents-and-matching-nested/209088/2 "2019-12-02T15:47:52Z")

</div>

This was resolved by searching:

```auto
{
    "_source": {
        "includes": ["*"],
        "excludes": ["c_purchases"]
    },
    "query": {
        "bool": {
            "must": {
                "match": {
                    "i_description": "corn"
                }
            },
            "should": {
                "nested" : {
                    "path" : "c_purchases",
                    "inner_hits": {
                        "_source": ["*"]
                    },
                    "query" : {
                        "match": {
                            "c_purchases.customernumber": "1111"
                        }
                    }
                }   
            }
        }
    }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 30, 2019, 3:47pm UTC](https://discuss.elastic.co/t/elastic-search-all-parents-and-matching-nested/209088/3 "2019-12-30T15:47:55Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.