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

Each document in the database is linked to specific profileId which in turn has multiple attributes
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

Attribute value = 3 and attributeId = 2 (the value is < 30)
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?

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. — Sam, Dec 22 '15 at 00:15
@Rob I have placed the document and index mapping in this link [https://jsfiddle.net/hsalman/rknhsc58/#] The Index mapping in the css window and the document mapping in JS section — Hussein Salman, Dec 22 '15 at 09:22
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. — Hussein Salman, Dec 22 '15 at 12:17
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. — Hussein Salman, Dec 24 '15 at 18:09

score 2 · Accepted Answer · answered Dec 24 '15 at 18:17

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"
              }
            }
          ]
        }
      }
    }
  }
}

Nest ElasticSearch: Boolean Search using Nested Query and Nested Objects

1 Answers1

Linked