# Strange results when querying nested objects

**URL:** <https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682>\
**Category:** Elasticsearch\
**Created:** [September 16, 2016, 9:19am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682 "2016-09-16T09:19:07Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![trex](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trex/32/34230_2.png) [@trex](https://discuss.elastic.co/u/trex)\
**Post date:** [September 16, 2016, 9:19am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/1 "2016-09-16T09:19:07Z")

</div>

**Elasticsearch version** : 2.3.3  
**Plugins installed** : no plugin  
**JVM version** : 1.8.0\_91  
**OS version** : Linux version 3.19.0-56-generic (Ubuntu 4.8.2-19ubuntu1)

I get strange results when I query [nested objects](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) on multiple paths. I want to search for all `female` with `dementia`. And there are matched patients among the results. But I also get other diagnoses I'm not looking for, the diagnoses related to these patients.

For example, I also get the following diagnoses despite the fact that I looked only for `dementia`.

- Mental disorder, not otherwise specified
- Essential (primary) hypertension

Why is that?  
I want to get **only** `female` with `dementia` and don't want other diagnoses.

`Client_Demographic_Details` contains one document per patient. `Diagnosis` contains multiple documents per patient. The ultimate goal is to index my whole data from PostgreSQL DB (72 tables, [over 1600 columns](http://stackoverflow.com/questions/12606842/what-is-the-maximum-number-of-columns-in-a-postgresql-select-query) in total) into Elasticsearch.

**Query:**

```
{'query': {
       'bool': {
           'must': [
               {'nested': {
                   'path': 'Diagnosis',
                   'query': {
                       'bool': {
                           'must': [{'match_phrase': {'Diagnosis.Diagnosis': {'query': "dementia"}}}]
                       }  
                   }
               }},
               {'nested': {
                   'path': 'Client_Demographic_Details',
                   'query': {
                       'bool': {
                           'must': [{'match_phrase': {'Client_Demographic_Details.Gender_Description': {'query': "female"}}}]
                       }  
                   }
               }}
           ]
       }
    }}

```

**Results:**

```
{
  "hits": {
    "hits": [
      {
        "_score": 3.4594634, 
        "_type": "Patient", 
        "_id": "72", 
        "_source": {
          "Client_Demographic_Details": [
            {
              "Gender_Description": "Female", 
              "Patient_ID": 72, 
            }
          ], 
          "Diagnosis": [
            {
              "Diagnosis": "F00.0 - Dementia in Alzheimer's disease with early onset", 
              "Patient_ID": 72, 
            }, 
            {
              "Patient_ID": 72, 
              "Diagnosis": "F99.X - Mental disorder, not otherwise specified", 
            }, 
            {
              "Patient_ID": 72, 
              "Diagnosis": "I10.X - Essential (primary) hypertension", 
            }
          ]
        }, 
        "_index": "denorm1"
      }
    ], 
    "total": 6, 
    "max_score": 3.4594634
  }, 
  "_shards": {
    "successful": 5, 
    "failed": 0, 
    "total": 5
  }, 
  "took": 8, 
  "timed_out": false
}

```

**Mapping:**

```
{
  "denorm1" : {
    "aliases" : { },
    "mappings" : {
      "Patient" : {
        "properties" : {
          "Client_Demographic_Details" : {
            "type" : "nested",
            "properties" : {
              "Patient_ID" : {
                "type" : "long"
              },
              "Gender_Description" : {
                "type" : "string"
              }
            }
          },
          "Diagnosis" : {
            "type" : "nested",
            "properties" : {
              "Patient_ID" : {
                "type" : "long"
              },
              "Diagnosis" : {
                "type" : "string"
              }
            }
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "creation_date" : "1473974457603",
        "number_of_shards" : "5",
        "number_of_replicas" : "1",
        "uuid" : "Jo9cI4kRQjeWcZ7WMB6ZAw",
        "version" : {
          "created" : "2030399"
        }
      }
    },
    "warmers" : { }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 16, 2016, 10:26am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/2 "2016-09-16T10:26:59Z")

</div>

If your document represents a single patient why is Client\_Demographic\_Details an array? Do you deliberately allow for multiple IDs and genders for a patient?

---

<div class="post-metadata">

**Author:** ![trex](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trex/32/34230_2.png) [@trex](https://discuss.elastic.co/u/trex)\
**Post date:** [September 16, 2016, 10:27am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/3 "2016-09-16T10:27:49Z")

</div>

Matched doc on nested will be inside `inner hits` and rest in source.  
This working query from here [http://stackoverflow.com/questions/39527012/strange-results-when-querying-nested-objects](http://stackoverflow.com/questions/39527012/strange-results-when-querying-nested-objects)

```
{
  "_source": {
    "exclude": [
      "Client_Demographic_Details",
      "Diagnosis"
    ]
  },
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "Diagnosis",
            "query": {
              "bool": {
                "must": [
                  {
                    "match_phrase": {
                      "Diagnosis.Diagnosis": {
                        "query": "dementia"
                      }
                    }
                  }
                ]
              }
            },
            "inner_hits": {}
          }
        },
        {
          "nested": {
            "path": "Client_Demographic_Details",
            "query": {
              "bool": {
                "must": [
                  {
                    "match_phrase": {
                      "Client_Demographic_Details.Gender_Description": {
                        "query": "female"
                      }
                    }
                  }
                ]
              }
            },
            "inner_hits": {}
          }
        }
      ]
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![trex](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trex/32/34230_2.png) [@trex](https://discuss.elastic.co/u/trex)\
**Post date:** [September 16, 2016, 10:28am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/4 "2016-09-16T10:28:46Z")

</div>

No, there is only one patient id per patient.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 16, 2016, 10:34am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/5 "2016-09-16T10:34:34Z")

</div>

So no need for arrays or "nested" mappings for that field then.

Technically speaking you only need to make the diagnoses "nested" too if your queries will test more than one property of each object each e.g. (psuedo code) `description:dementia AND diagnoser:DrSmith`. However your example was only querying the diagnosis description so if that's all you do there's no harm in just having a non-nested property for diagnosis.

---

<div class="post-metadata">

**Author:** ![trex](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trex/32/34230_2.png) [@trex](https://discuss.elastic.co/u/trex)\
**Post date:** [September 16, 2016, 10:41am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/6 "2016-09-16T10:41:00Z")

</div>

I want to run boolean queries to test multiple properties from multiple nested objects. I plan to have 72 nested objects per patient. Each nested object contains data from the related PostgreSQL table.

So, you are saying that I need to have `Client_Demographic_Details` not nested and `Diagnosis` as nested object in the same document, aren't you? What benefits does the solution has against "all nested" objects solution that I have?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 16, 2016, 11:15am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/7 "2016-09-16T11:15:09Z")

</div>

> [@trex](#):
>
> you are saying that I need to have Client\_Demographic\_Details not nested and Diagnosis as nested object in the same document, aren't you?

If there is only ever one Client\_Demographic\_Details object then that definitely does not need to be "nested".  
Even with your diagnoses as non-nested you can happily query for people who have `demograpic gender:female AND diagnosis:dementia`.  
The reason you reach for "nested" is only if you have criteria that needs to test more than one term in individual diagnosis objects. This is the "cross-matching" problem I illustrate in this example [1] using students and their examination results - the query is for people who have an exam result with a particular subject AND grade. Without "nested" the grades and subjects for a person's exams are muddled.  
Hope this makes sense.  
Because users have a hard time articulating complex nested logic, sometimes a "flat" system is preferable and users may actually want the laxness of cross-matching rather than the stricter controls used for nested.

[1] [Proposal for nested document support in Lucene | PPT](http://www.slideshare.net/MarkHarwood/proposal-for-nested-document-support-in-lucene)

---

<div class="post-metadata">

**Author:** ![trex](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trex/32/34230_2.png) [@trex](https://discuss.elastic.co/u/trex)\
**Post date:** [September 16, 2016, 11:22am UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/8 "2016-09-16T11:22:12Z")

</div>

Ok @Mark_Harwood, I will make `Client_Demographic_Details` as root object, try it and let you know.

---

<div class="post-metadata">

**Author:** ![trex](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/trex/32/34230_2.png) [@trex](https://discuss.elastic.co/u/trex)\
**Post date:** [September 16, 2016, 1:29pm UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/9 "2016-09-16T13:29:04Z")

</div>

I tried your solution, it works. I agree it looks nicer from the hyrarchy point of view.

---

<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:** [July 5, 2017, 10:19pm UTC](https://discuss.elastic.co/t/strange-results-when-querying-nested-objects/60682/10 "2017-07-05T22:19:41Z")

</div>