Elasticsearch multiple filters, sum and group by

Question

I'm very new to Elasticsearch and currently trying to translate a MySQL query into Elasticsearch one. The documents are stored under "Json_logs" index in ES.

Document:

{
    "bid_id": "16613393",
    "user_id": "f63edcf2-e353-4c50-8a6e-290f973b320e",
    "date": "2016-01-03",
    "timestamp1": 1451861391,
    "timestamp2": null,
    "zone": {
        "zone_id": 124519,
        "tag_type": null,
        "in_iframe": null,
        "document_referrer": "http%3A%2F%2Fwww.celebritynetworth.com%2Frichest-celebrities%2Fsingers%2Fmichael-jackson-net-worth%2F",
        "cost": 0.4,
        "parent_zone_id": null
    },
    "device": {
        "ip": "174.52.96.91",
        "ua": "Mozilla/5.0 (iPad; CPU OS 9_2 like Mac OS X) AppleWebKit/601.1.46 (KHTML, like Gecko) Version/9.0 Mobile/13C75 Safari/601.1",
        "device_code": 5,
        "device_type": "Tablet"
    },
    "bid": {
        "page_url": null,
        "floor_price": 0.3,
        "buyers": []
    },
    "impression": {
        "buyer_id": null,
        "win_price": 0.3,
        "banner_id": 1029526
    }
}

MySQL query:

SELECT SUM(floor_price) 
FROM Json_logs
WHERE zone_id=124519 AND floor_price=0.2
GROUP BY zone_id;

What would be the equivalent query in Elasticsearch for the above MySQL query? Thank you!

WHERE zone_id=124519 AND floor_price=0.2 isn't GROUP BY zone_id redundant ? — keety
– keety, Commented Feb 4, 2016 at 22:53
you're right. it's redundant. can you help me with translate the query into elasticsearch query? — coding under the influence
– coding under the influence, Commented Feb 4, 2016 at 23:22

Richa · Accepted Answer · 2016-02-05 05:37:10Z

1

Try using following query:

{
"query": {
    "bool": {
        "must": [
           {
               "term": {
                     "zone.zone_id": 124519
               }
           },
           {
               "term": {
                     "bid.floor_price": 0.2
               }
           }
        ]
    }
},
"aggs" : {
    "group_by_zone" : {

     "terms" : {
      "field"    : "zone.zone_id"
     },
     "aggs" : {

     "sum_floor_price"   : {
            "sum" :  {
             "field"    : "bid.floor_price"       
            }

        }   
      }

     }

    }
  }

answered Feb 5, 2016 at 5:37

Richa

7,6796 gold badges30 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Elasticsearch multiple filters, sum and group by

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related