Nest ElasticSearch: Boolean Search using Nested Query and Nested Objects

Question

I am using Nest Elastic and building the query for a Boolean search using Head plugin , i am combining multiple queries

Notes about DB Structure and Elastic Mapping

1. Each document in the database is linked to specific profileId which in turn has multiple attributes
2. Each document has multiple attribute values associated with it

In this query, i am trying to get all documents which has specific profile and attribute value > 30 keeping in mind that this attribute should have the attribute Id 2 only.

The SQL Query:

Select av.*, d.name from document d inner join attributeValue av on d.DocumentId = av.DocumentId where d.profileid = 1 and av.AttributeId = 2 and av.Intvalue >30

Elastic Query

   { "query": {
    "bool": {
    "must": [
    {
       "term": { "Document.profileid": "1"  }
    }
    ,
    {
      "term": {"Document.lstChildren.AttributeID": "2" }
    }
    ,
    { 
      "range": { "Document.lstChildren.IntValue": { "gt": "30"} }
    }
    ,
    {
    "match_all": { }
    }
    ],
    "must_not": [ ],
    "should": [ ]
    }
    },   "from": 0, "size": 10, "sort": [ ], "facets": { }
    }

Problem

The result also contains a document that has the following attribute values

1. Attribute value = 3 and attributeId = 2 (the value is < 30)
2. Attribute value = 34 but with attributeId different than 2 (incorrect)

This document must not be included as it doesn't satisfy my needs.

How can i build this query?

Fatigue

May you share sample document and index mapping? – Agostino

Can you try removing the "match_all": {} query from your must list? It's totally unnecessary and I wouldn't be surprised if there is some bug popping up because of it. – Ranket

@Ranket match_all": {} didn't solve the problem – Fatigue

@Agostino I have placed the document and index mapping in this link [jsfiddle.net/hsalman/rknhsc58/#] The Index mapping in the css window and the document mapping in JS section – Fatigue

The problem in the DSL query: it is checking on documents that should have attributeId and IntValue in any of its children array . But we have to build the query so that it satisfies both conditions on at least one of its lstChildren array. – Fatigue

The Solution is to change the mapping first by making lstChildren a nested object. Then using nested query will ensure that all conditions are met as specified. – Fatigue

Answer 1

The Solution is to change the mapping first by making lstChildren a nested object. Then using nested query will ensure that all conditions are met as specified. The nested query below specifies two conditions that returns only expected results, but i used "Equal" instead of "greater than" for the "IntValue" in order keep it simple:

{
  "query": {
    "nested": {
      "path": "lstChildren",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "lstChildren.AttributeID":"2"
              }
            },
            {
              "match": {
                "lstChildren.IntValue": "31"
              }
            }
          ]
        }
      }
    }
  }
}

Fatigue