Aggregate return all data

Elastic Stack Elasticsearch
1

Hi,
Following is my query
GET product_detail_info/_search
{
"query": {
"multi_match": {
"query": "a",
"fields": ["item_name", "description","cat_name","item_details"]
}
},
"aggs": {
"search_result": {
"terms": {
"field": "product_id"
}
}
}
}

I am new to elasticsearch its my assumption it should work like group by clause in sql but its returning all data ( multiple record with same product id ) following are the response.

        [      
      {
      "_index" : "product_detail_info",
      "_type" : "doc",
      "_id" : "41638564138017228075",
      "_score" : 8.214151,
      "_source" : {
        "brand_id" : 232,
        "item_id" : 8564,
        "item_details" : """
        <ul>
        <li>95% cotton; 5% spandex</li>
        <li>A-line style</li>
        <li>Zip closure</li>
        <li>Imported</li>
        </ul>
        """,
        "document_id" : "41638564138017228075",
        "item_image" : "5a9932bd107ad.png",
        "pro_updated_date" : "2020-03-04T19:23:12.000Z",
        "image_sort_order" : 1,
        "image_product_name" : "5a97d4efba0e6.jpg",
        "item_size_id" : 8075,
        "updated_at" : "2020-03-04T19:23:12.000Z",
        "control_number" : "STS6689",
        "@version" : "1",
        "item_color_id" : 1722,
        "description" : "<p>A godet panel adds volume to this slim A-line denim mini that&rsquo;s equally&nbsp;modern and preppy.</p>",
        "image_id" : 4163,
        "item_color_image" : "5a97d4b41e9f9.jpg",
        "image_title" : "Sugar Lips_STS6689_Pink_WR_R_FLAT-375x475",
        "item_v4_size_title" : "S",
        "item_sku" : "SS8564",
        "product_id" : 1380,
        "shopify_product_id" : 3958192767043,
        "item_name" : "Sugar Lips Put a Bow on It Skirt",
        "cat_shopify_collection_id" : null,
        "item_price" : 56.0,
        "display_product_color_id" : 1722,
        "cat_name" : "Mini",
        "@timestamp" : "2020-04-01T11:50:03.562Z",
        "gender" : "f"
      }
      },
      {
      "_index" : "product_detail_info",
      "_type" : "doc",
      "_id" : "41638565138017228076",
      "_score" : 7.8401,
      "_source" : {
        "brand_id" : 232,
        "item_id" : 8565,
        "item_details" : """
        <ul>
        <li>95% cotton; 5% spandex</li>
        <li>A-line style</li>
        <li>Zip closure</li>
        <li>Imported</li>
        </ul>
        """,
        "document_id" : "41638565138017228076",
        "item_image" : "5a9932bb3fd65.png",
        "pro_updated_date" : "2020-03-04T19:23:12.000Z",
        "image_sort_order" : 1,
        "image_product_name" : "5a97d4efba0e6.jpg",
        "item_size_id" : 8076,
        "updated_at" : "2020-03-04T19:23:12.000Z",
        "control_number" : "STS6689",
        "@version" : "1",
        "item_color_id" : 1722,
        "description" : "<p>A godet panel adds volume to this slim A-line denim mini that&rsquo;s equally&nbsp;modern and preppy.</p>",
        "image_id" : 4163,
        "item_color_image" : "5a97d4b41e9f9.jpg",
        "image_title" : "Sugar Lips_STS6689_Pink_WR_R_FLAT-375x475",
        "item_v4_size_title" : "M",
        "item_sku" : "SS8565",
        "product_id" : 1380,
        "shopify_product_id" : 3958192767043,
        "item_name" : "Sugar Lips Put a Bow on It Skirt",
        "cat_shopify_collection_id" : null,
        "item_price" : 56.0,
        "display_product_color_id" : 1722,
        "cat_name" : "Mini",
        "@timestamp" : "2020-04-01T11:50:03.562Z",
        "gender" : "f"
      }
  ]

help will be highly appreciated. I was expected it will work like group by clause.

thanks

2

@hariskhalique The response you posted is incomplete, as I'd expect your aggregation results are further down in the response. Whenever you run a search, Elasticsearch will respond with search hits. So your query will give you both the top 10 (by default) matching records as well as the aggregation you requested.

Try running the query with size=0 like:

GET product_detail_info/_search?size=0

or

GET product_detail_info/_search
{
  "size": 0,
  "query": {
  ...
}

That way, the only response you'll get is the aggregation, which should have one bucket per product_id with a count of the matching results in each one. If you want matching documents to be returned with each bucket, look at including a top hits aggregation under the terms agg or check out field collapsing

3

Stille having multiple record with same product ids

  GET product_detail_info/_search
  {
    "aggs": {
      "results": {
        "terms": {
          "field": "product_id",
          "size": 10
        },"aggs": {
          "result": {
             "top_hits": {
              "_source": {"includes": [
                    "item_name",
                    "cat_name",
                    "product_id"
                ]}
            }
          }
        }
      }
    }
  }

return data is

{
  "_index" : "product_detail_info",
  "_type" : "doc",
  "_id" : "1715781892633110038286162314",
  "_score" : 1.0,
  "_source" : {
    "cat_name" : "T-Shirts",
    "product_id" : 31100,
    "item_name" : "Kimber Slinky Jersey T-Shirt"
  }
},
{
  "_index" : "product_detail_info",
  "_type" : "doc",
  "_id" : "1715911892633110038286162314",
  "_score" : 1.0,
  "_source" : {
    "cat_name" : "T-Shirts",
    "product_id" : 31100,
    "item_name" : "Kimber Slinky Jersey T-Shirt"
  }
}

any suggestion.

4

@hariskhalique you still need to set size=0.

  GET product_detail_info/_search
  {
     "size": 0, 
     "aggs": {
      "results": {
        "terms": {
          "field": "product_id",
          "size": 10
        },"aggs": {
          "result": {
             "top_hits": {
              "_source": {"includes": [
                    "item_name",
                    "cat_name",
                    "product_id"
                ]}
            }
          }
        }
      }
    }
  }
1 Like
5

Thanks It works do we have any way to add multiple fileds aggs

6

Not sure what you mean. You can get multiple aggs in one request like

GET my-index/_search?size=0
{
  "aggs": {
    "1": { ... },
    "2": { ... },
  }
}

or do nested aggs like you're doing with top_hits

GET my-index/_search?size=0
{
   "aggs": {
      "1": {
        ...
        ,
        "aggs": {
           "2": { ... }
        }
   }
}

Either what you had in mind?

1 Like
7

Man you are awesome. One final question sql we wrote column1 = column2 how we can do that in ES.

All above answer helps me a lot I am really thanks full to you.

8

Happy to help!

Think the only way to do what you want in a single query is to do a scripted query

GET /_search
{
    "query": {
        "bool" : {
            "filter" : {
                "script" : {
                    "script" : {
                        "source": "doc['column1'].value == doc['column2'].value",
                        "lang": "painless"
                     }
                }
            }
        }
    }
}
9

One more question how we use wild card. I try like this

  "query": {
      "wildcard": {
        "item_name": {
            "value": "*Iko*",
            "boost": 1.0,
            "rewrite": "constant_score"
        }
  }

and like this

"match": {
  "item_name": "*Ik"
}

and like this

"regexp": {
  "item_name": "^Iko"
}

data have value

{
              "_index" : "product_detail_info",
              "_type" : "doc",
              "_id" : "1733951907733120138583162807",
              "_score" : 6.0950193,
              "_source" : {
                "image_product_name" : "5dd90d61e82a7.jpg",
                "item_sku" : "SS190773",
                "item_id" : 190773,
                "cat_name" : "T-Shirts",
                "product_id" : 31201,
                "item_name" : "Ikonik Karl Pocket T-Shirt"
              }

and I want to apply same search on more then one fields

10

Please create another question for this: it'll help others find it when looking for similar help!

11

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.