# Constructing complex NEST query

**URL:** <https://discuss.elastic.co/t/constructing-complex-nest-query/246134>\
**Category:** Elasticsearch\
**Tags:** language-clients\
**Created:** [August 24, 2020, 2:20pm UTC](https://discuss.elastic.co/t/constructing-complex-nest-query/246134 "2020-08-24T14:20:42Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![gswartz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gswartz/32/45582_2.png) [@gswartz](https://discuss.elastic.co/u/gswartz)\
**Post date:** [August 24, 2020, 2:20pm UTC](https://discuss.elastic.co/t/constructing-complex-nest-query/246134/1 "2020-08-24T14:20:42Z")

</div>

Our index consists of a text field for notes. The notes can be tied to multiple entity types in our system such as a job, worker, company, etc. So, I need to somehow construct the search query based on what type of entity the user wants to restrict the notes type to. So, for example, if a user is looking for all notes with a specific keyword that are also company notes, I want to do something like below (I think).

```auto
var searchResponse = client.Search<NotesAll>(s => s
    .From(viewModel.Page * retval.PageSize)
    .Size(retval.PageSize)
    .Query(q => q
         .Match(m => m
            .Field(f => f.Notes)
            .Query(viewModel.Keyword.ToLower())
         )
    )
    .Query(q => q.Exists(y => y.Field(x => x.Company)))
    .Sort(x => x.Descending(y => y.LastModifiedDate))
);

```

Trying to match the keyword in the notes where also, the company field is not null.

The problem I have is that I have to create the second Query for Exists based on what type of search they're running and I'm not sure how to do that without having a bunch of case statements. I was hoping with NEST I would have a search object that I could add additional criteria to. Maybe it does and I'm just not seeing how. Any thoughts? Thanks!

---

<div class="post-metadata">

**Author:** ![gswartz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gswartz/32/45582_2.png) [@gswartz](https://discuss.elastic.co/u/gswartz)\
**Post date:** [August 24, 2020, 6:06pm UTC](https://discuss.elastic.co/t/constructing-complex-nest-query/246134/2 "2020-08-24T18:06:46Z")

</div>

I eventually patched something together that kind of works, but not what I'm looking for. Rather than limiting it to records that have a match to my search terms AND also have a value in the company\_id field, it appears to be disregarding the keyword and returning 10000 results.

```auto
var queryContainer = new List<QueryContainer>();
var mustContainer = new List<QueryContainer>();

foreach (var term in terms)
{
    queryContainer.Add(new TermQuery { Field = "notes", Value = term.ToLower()});
}

if (viewModel.Company)
    mustContainer.Add(new ExistsQuery
    {
        Field = "company_id",
    });

var boolQuery = new BoolQuery()
{
    Should = queryContainer,
    Must = mustContainer
};

var searchRequest = new SearchRequest<NotesAll>
{
    From = viewModel.Page * retval.PageSize,
    Size = retval.PageSize,
    Query = boolQuery
};

```

The resulting json from this query is this.

```auto
{
    "from": 0,
    "query": {
        "bool": {
            "must": [{
                "exists": {
                    "field": "company_id"
                }
            }],
            "should": [{
                "term": {
                    "notes": {
                        "value": "geoff"
                    }
                }
            }, {
                "term": {
                    "notes": {
                        "value": "smith"
                    }
                }
            }]
        }
    },
    "size": 20
}

```

So, how can I change this in order to get any keywrod matches for (geoff or smith) in notes AND requiring of a value in the company\_id field? Thanks.

---

<div class="post-metadata">

**Author:** ![gswartz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gswartz/32/45582_2.png) [@gswartz](https://discuss.elastic.co/u/gswartz)\
**Post date:** [August 24, 2020, 8:28pm UTC](https://discuss.elastic.co/t/constructing-complex-nest-query/246134/3 "2020-08-24T20:28:56Z")

</div>

I think I figured it out. I changed should to filter and that seems to give me what I'm looking for.

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [August 24, 2020, 10:38pm UTC](https://discuss.elastic.co/t/constructing-complex-nest-query/246134/4 "2020-08-24T22:38:16Z")

</div>

Glad to hear that you have some working for you, @gswartz.

One thing about `should` clauses with the presence of a `must` clause is that a document does not have to be a match for _any_ of them i.e. none of them have to match. In this scenario, `should` clauses act as a boosting signal to calculating a relevancy score in a _more is better_ approach. If this is not the behaviour desired and _at least_ one of the `should` clauses must match, you can use the `minimum_should_match` parameter to specify a number or percentage of them that must match.

---

<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:** [September 21, 2020, 10:38pm UTC](https://discuss.elastic.co/t/constructing-complex-nest-query/246134/5 "2020-09-21T22:38:20Z")

</div>

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