Query on response value

user_17207651 0
Viewed 60

How to write a conditional query on multiple fields? Explain: I have an index let say "case" and the fields are "title", "secondTitle(contain nested obj)", "source(contain nested obj)". Now I want to search on title and want number of document inside of secondTitle, which also contains in source field of another document.

PUT /case_indx_tmp_tmp
{
  "mappings": {
    "properties": {
      "title":{
        "type": "text",
        "fields": {
          "title":{
            "type":"keyword"
          }
        }
      },
      "secondTitle":{
        "type": "nested",
        "properties": {
          "second_title":{
            "type":"text",
            "fields": {
              "secondtitle":{
                "type":"keyword"
              }
        }
          }
        }
      },
      "source":{
        "type": "nested",
        "properties": {
          "source_title":{
            "type":"text",
            "fields": {
              "sourcetitle":{
                "type":"keyword"
              }
        }
          }
        }
      }
    }
  }
}

PUT /case_indx_tmp_tmp/_doc/1
{
    "title" : "Case 1",
    "secondTitle" : [
        {
            "case_title" : "Case 2"
        }
    ],
    "source":[
      {
        "source_title":"Case 3"
      },
      {
        "source_title":"Case 4"
      }
    ]
}
PUT /case_indx_tmp_tmp/_doc/2
{
    "title" : "Case 2",
    "secondTitle" : [
        {
            "case_title" : "Case 3"
        },
        {
            "case_title" : "Case 4"
        },
        {
            "case_title" : "Case 1"
        }
    ],
    "source":[
      {
        "source_title":"Case 1"
      }
    ]
}
PUT /case_indx_tmp_tmp/_doc/3
{
    "title" : "Case 3",
    "secondTitle" : [
        {
            "case_title" : "Case 5"
        },
        {
            "case_title" : "Case 4"
        },
        {
            "case_title" : "Case 1"
        }
    ],
    "source":[
      {
        "source_title":"Case 2"
      },
      {
        "source_title":"Case 5"
      }
    ]
}
PUT /case_indx_tmp_tmp/_doc/4
{
    "title" : "Case 5",
    "source":[
      {
        "source_title":"Case 3"
      },
      {
        "source_title":"Case 4"
      },
      {
        "source_title":"Case 2"
      }
    ]
}

if search term is "Case" then it will return title: Case 1 secondTitle_count: 1

title: Case 2 secondTitle_count: 3

title: Case 3 secondTitle_count: 2

title: Case 5 secondTitle_count: 0

my solution was, to iterate all documents which return after search query and make new bool query object for the "source" field where values are from secondTitle.

kibana elasticsearch mongodb django python
1 Answers

You can use a combination of terms aggregation and nested aggregation with value count aggregation.

In the below query,

  1. The match query is used to get only those documents that contain case in the title field.

  2. Terms aggregation will create a bucket of all the unique field values of the title field.

  3. Nested aggregation along with Value count aggregation will aggregate over the terms aggregation result, and get the count of documents present under secondTitle object.


POST case_indx_tmp_tmp/_search
{
  "size": 0,
  "query": {
    "match": {
      "title": "case"
    }
  }, 
  "aggs": {
    "top_tags_per_comment": {
      "terms": {
        "field": "title.title"
      },
      "aggs": {
        "Services": {
          "nested": {
            "path": "secondTitle"
          },
          "aggs": {
            "secondTitle_count": {
              "value_count": {
                "field": "secondTitle.case_title.keyword"
              }
            }
          }
        }
      }
    }
  }
}

Search Result will be

"aggregations" : {
    "top_tags_per_comment" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Case 1",
          "doc_count" : 1,
          "Services" : {
            "doc_count" : 1,
            "secondTitle_count" : {
              "value" : 1
            }
          }
        },
        {
          "key" : "Case 2",
          "doc_count" : 1,
          "Services" : {
            "doc_count" : 3,
            "secondTitle_count" : {
              "value" : 3
            }
          }
        },
        {
          "key" : "Case 3",
          "doc_count" : 1,
          "Services" : {
            "doc_count" : 3,
            "secondTitle_count" : {
              "value" : 3
            }
          }
        },
        {
          "key" : "Case 5",
          "doc_count" : 1,
          "Services" : {
            "doc_count" : 0,
            "secondTitle_count" : {
              "value" : 0
            }
          }
        }
      ]
    }
  }
user_103487580
Related