Elasticsearch aggregation

Question

I'm looking how to merge elasticsearch hits into one. Basicly I want to do the equivalent of

SELECT COUNT(*) WHERE TOTO = 1 AND TATA = 2 AND TITI = 3

I've manage to deal with the WHERE part using filters but I get all the hits in separate entities so what I have is

SELECT TOTO, TATA, TITI WHERE TOTO = 1 AND TATA =2 AND TATA =3

How do I manage to get only one hit containing the COUNT(*) value ?

Environment

{
  "my_element": {
    "mappings": {
      "test": {
        "properties": {
          "baskets": {
            "type": "nested",
            "properties": {
              "basket_id": {
                "type": "string"
              },
              "num_basket": {
                "type": "integer"
              },
              "tp_basket": {
                "type": "string"
              }
            }
          },
          "test_id": {
            "type": "string"
          },
          "test_name": {
            "type": "string"
          }
        }
      }
    }
  }
}

so I want to count how many test element have a basket_id of X and a num_basket of 3 (if they do that means baskets are Identical, so I want also to show baskets field)

{
  "fields": [
    "bucket_list",
    "baskets.basket_id",
    "baskets.num_basket"
  ],
  "query": {
    "filtered": {
      "filter": {
        "and": {
          "filter": [
            {
              "nested": {
                "path": "baskets",
                "filter": {
                  "and": {
                    "filters": [
                      {
                        "or": {
                          "filters": [
                            {
                              "bool": {
                                "must": [
                                  {
                                    "term": {
                                      "baskets.basket_id": "40"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.num_basket": "1"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.tp_basket": "1"
                                    }
                                  }
                                ]
                              }
                            },
                            {
                              "bool": {
                                "must": [
                                  {
                                    "term": {
                                      "baskets.basket_id": "41"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.num_basket": "1"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.tp_basket": "1"
                                    }
                                  }
                                ]
                              }
                            },
                            {
                              "bool": {
                                "must": [
                                  {
                                    "term": {
                                      "baskets.basket_id": "342"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.num_basket": "1"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.tp_basket": "1"
                                    }
                                  }
                                ]
                              }
                            },
                            {
                              "bool": {
                                "must": [
                                  {
                                    "term": {
                                      "baskets.basket_id": "342"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.num_basket": "1"
                                    }
                                  },
                                  {
                                    "term": {
                                      "baskets.tp_basket": "1"
                                    }
                                  }
                                ]
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              }
            },
            {
              "nested": {
                "path": "baskets",
                "filter": {
                  "and": {
                    "filters": [
                      {
                        "bool": {
                          "must": [
                            {
                              "term": {
                                "baskets.basket_id": "15"
                              }
                            },
                            {
                              "term": {
                                "baskets.num_basket": "2"
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              }
            },
            {
              "nested": {
                "path": "baskets",
                "filter": {
                  "and": {
                    "filters": [
                      {
                        "bool": {
                          "must": [
                            {
                              "term": {
                                "baskets.basket_id": "15"
                              }
                            },
                            {
                              "term": {
                                "baskets.num_basket": "3"
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

and result

{
  "took": 5,
  "timed_out": false,
  "_shards": {
    "total": 2,
    "successful": 2,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "my_element",
        "_type": "test",
        "_id": "sMHPv3i4RTqNCIChGh4Iew",
        "_score": 1,
        "fields": {
          "baskets.basket_id": [
            "15",
            "15",
            "15"
          ],
          "baskets.num_basket": [
            2,
            3,
            1
          ]
        }
      },
      {
        "_index": "my_element",
        "_type": "test",
        "_id": "KL3U-g-7RtuusNV8hi9YHQ",
        "_score": 1,
        "fields": {
          "baskets.basket_id": [
            "15",
            "15",
            "15"
          ],
          "baskets.num_basket": [
            1,
            2,
            3
          ]
        }
      }
    ]
  }
}

You've showed details related to how to be done in SQL but no details about your current ES environment. What exactly you want to count? Are these indices, types, documents? Give example from your ES environment. — Andrei Stefan
– Andrei Stefan, Commented Nov 26, 2014 at 14:26

Kavya Rani · Accepted Answer · 2016-02-15 14:27:23Z

3

Probably I am answering really late, but for those who are looking for the answer. This query can be written in very simple way, as follows.

GET _count
{
"query": 
{
  "bool" : {
    "must" : [ {
      "term" : {
        "basket_id" : "X"
      }
    }, {
      "term" : {
        "num_basket" : 3
      }
    } ]
  }
}
}

The above query will give you following result.

{
   "count": 6,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   }
}

You can read the count from above response, which is your desired result.

Hope this was helpful.

answered Feb 15, 2016 at 14:27

Kavya Rani

3712 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Olly Cruickshank · Accepted Answer · 2014-11-26 14:48:37Z

0

When the search results are returned from Elasticsearch you should see a total field - this has the count of matching documents.

for example, see "total" : 2, below:

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 12,
    "successful" : 12,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 2.098612,
    "hits" : [ {
      "_index" : "mytest",
      "_type" : "message",
      "_id" : "P9wGgJHjQmK8GUvw8M5Q8A",
      "_score" : 2.098612,
      "fields" : {
        "tata" : [ "1" ],
        "toto" : [ "1" ],
        "titi" : [ "2" ]
      }
    }, {
      "_index" : "mytest",
      "_type" : "message",
      "_id" : "M26ychoyRR6HkordRdS_HA",
      "_score" : 0.30685282,
      "fields" : {
        "tata" : [ "1" ],
        "toto" : [ "1" ],
        "titi" : [ "2" ]
      }
    } ]
  }
}

answered Nov 26, 2014 at 14:48

Olly Cruickshank

6,2094 gold badges36 silver badges30 bronze badges

4 Comments

Xarouma Over a year ago

Yes, but I need to have a resultset like "tata" : '1', 'toto' : 2 'titi' : 3, "cout" : 3

Olly Cruickshank Over a year ago

is "tata":'1' a count of matching records (i.e. only one tata record matched) or the value of the data field for tata?

Xarouma Over a year ago

it is a value of the field tata

Olly Cruickshank Over a year ago

in that case what you're asking for isn't possible - your application needs to get the data from hits.total not from within the fields object.

Collectives™ on Stack Overflow

Elasticsearch aggregation

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related