elasticsearchlogstashkibanaelasticsearch-5kibana-6

How to write ElasticSearch Query on the basis of distinct objects?


Here I am trying to get the distinct attribute name on the basis of tenant_id and hierarchy_name , this is my data which is Indexed

       {
      "hits": [
        {
          "_index": "emp_indexs_datas_d_v",
          "_type": "bulkindexing",
          "_id": "84",
          "_source": {
            "id": "2",
            "name": "PRODUCT",
            "values": "GEO"
          }
        },
        {
          "_index": "emp_indexs_datas_d_v",
          "_type": "bulkindexing",
          "_id": "88",
          "_source": {
            "id": "1",
            "name": "CUSTOMER",
            "values": "CUSTOMER_OPEN_1"
          }
        },
        {
          "_index": "emp_indexs_datas_d_v",
          "_type": "bulkindexing",
          "_id": "98",
          "_source": {
            "id": "2",
            "name": "PRODUCT",
            "values": "CUSTOMER_OPEN_2"
          }
        },
        {
          "_index": "emp_indexs_datas_d_v",
          "_type": "bulkindexing",
          "_id": "100",
          "_source": {
            "id": "1",
            "name": "CUSTOMER",
            "values": "CUSTOMER-ALL"
          }
        },
 {
          "_index": "emp_indexs_datas_d_v",
          "_type": "bulkindexing",
          "_id": "99",
          "_source": {
            "id": "2",
            "name": "CUSTOMER",
            "values": "CUSTOMER_OPEN_2"
          }
      ]
    }

This is the query which was trying here , I was getting the distinct attribute_name on the basis of hierarchy_name

{
        "query": {
            "multi_match": {
                "query": "CUSTOMER",
                "fields": [
                    "hierarchy_name"
                ]
            }
        },
        "collapse": {
            "field": "attribute_name.keyword"
        }
    }

Now I want to match one more property tenant_id , previously I was matching with hierarchy_name ,can someone help me with the query

output expected . like suppose for tenant_id 2 and hierarchy_name PRODUCT we get

{
  "hits": [
    {
      "_index": "emp_indexs_datas_d_v",
      "_type": "bulkindexing",
      "_id": "84",
      "_source": {
        "tenant_id": "2",
        "hierarchy_name": "CUSTOMER",
        "attribute_name": "GEO"
      }
    },
    {
      "_index": "emp_indexs_datas_d_v",
      "_type": "bulkindexing",
      "_id": "98",
      "_source": {
        "tenant_id": "2",
        "hierarchy_name": "CUSTOMER",
        "attribute_name": "CUSTOMER_OPEN_2"
      }

    }
  ]
}

Solution

  • You can use a combination of bool/must clause to combine multiple conditions

    {
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "tenant_id": 2
              }
            },
            {
              "multi_match": {
                "query": "PRODUCT",
                "fields": [
                  "hierarchy_name"
                ]
              }
            }
          ]
        }
      },
      "collapse": {
        "field": "attribute_name.keyword"
      }
    }
    

    Search Result will be

    "hits": [
          {
            "_index": "67379727",
            "_type": "_doc",
            "_id": "1",
            "_score": 1.4144652,
            "_source": {
              "tenant_id": "2",
              "hierarchy_name": "PRODUCT",
              "attribute_name": "GEO"
            },
            "fields": {
              "attribute_name.keyword": [
                "GEO"
              ]
            }
          },
          {
            "_index": "67379727",
            "_type": "_doc",
            "_id": "3",
            "_score": 1.4144652,
            "_source": {
              "tenant_id": "2",
              "hierarchy_name": "PRODUCT",
              "attribute_name": "CUSTOMER_OPEN_2"
            },
            "fields": {
              "attribute_name.keyword": [
                "CUSTOMER_OPEN_2"
              ]
            }
          }
        ]