Elasticsearch-hadoop updated path

If you are seeing this error I would double check your package name. elasticsearch-hadoop updated their class path org.elasticsearch.pig.EsStorage to org.elasticsearch.hadoop.pig.EsStorage

Nested Aggregations

I was looking at aggregations today and found that aggregations had the same problem with grouping as doing an and between two fields in a sub document. This makes sense given the way Lucene stores multi field documents. When stored without nested it will group all values for a field together. I will walk through how to avoid some problems that can arise.

First lets set-up our data.

Create Schema with nested Response Index some nested documents Index some documents with a default mapping Query for aggregations with the default mapping Response We can see that the numbers are way too high. There is only one dogwood tree in all the parks and it says the total height is 179. This because when it groups on dogwood, it cannot tell what height belongs to it. So for dogwood it gives the total for all the trees in the park.

To avoid this we need to make the tree type nested.
Query for aggregations with nested Response As you can see when I query on the nested index I get the expected counts.

Below I show a limitation of nest when doing aggregations. Since nested creates another Lucene under the covers it cannot reach back out of that Lucene index to aggregate data.

Query for aggregations with nested but, will reach outside the sub document Response There are no results due to the field being summed not being found. To work around this the "terms" field would have to be moved out of the nested document. Alternatively, you can push the data being summed into each of nested documents.

Intro to Elasticsearch and New Features in version 1.0.x (Aggregations)

These are the notes from my presentation: http://www.meetup.com/Elasticsearch-Philadelphia/events/164506092/

http://youtu.be/6Idi13rQx1o?t=12m39s

Elasticsearch introduced aggregations into their 1.0.0 version.  This allows you to create analytics's for your data inside Elasticsearch. 

The data for this was provided by the City of Philadelphia.  James Tyack transformed the data into Elasticsearch's format for loading.

https://github.com/CityOfPhiladelphia/phl-energy-benchmarking-data/blob/master/Benchmarking_Data_Public.csv

https://github.com/jamestyack/rubyOpenDataTools/blob/master/philly_energy_benchmark_data/phl_energy_benchmarking_elastic_upload.json


The data model.  Some notes anything I intend to sum or average I made either a double or long.  Also, I used a keyword tokenizer for "PROPERTY_TYPE" to keep the categories together.  Otherwise the aggregations would have rolled up by a single word and not the whole category. 
$ curl -XPUT 'http://localhost:9200/philly_energy' -d '
{
    "settings": {
        "number_of_shards": 5,
        "analysis": {
            "analyzer": {
                "key": {
                    "tokenizer": "keyword",
                    "filter": [
                        "lowercase"
                    ]
                }
            }
        }
    },
    "mappings": {
        "building": {
            "properties": {
                "WEATHER_NORMALIZED_SITE_EUI": {
                    "type": "double"
                },
                "ELECTRIC_USE": {
                    "type": "long"
                },
                "TOTAL_GHG_EMISSIONS": {
                    "type": "double"
                },
                "OIL_USE": {
                    "type": "double"
                },
                "SECTOR": {
                    "type": "string"
                },
                "PERCENT_BETTER": {
                    "type": "double"
                },
                "BLDG_FLOOR_AREA": {
                    "type": "long"
                },
                "ENERGY_STAR_SCORE": {
                    "type": "string"
                },
                "YEAR_BUILT": {
                    "type": "long"
                },
                "GAS_USE": {
                    "type": "double"
                },
                "STEAM_USE": {
                    "type": "double"
                },
                "SITE_EUI": {
                    "type": "double"
                },
                "ASSET_ZIP": {
                    "type": "string"
                },
                "SOURCE _EUI": {
                    "type": "double"
                },
                "ASSET_ADDR": {
                    "type": "string"
                },
                "FACILITY_DESCRIPTION": {
                    "type": "string"
                },
                "PROPERTY_TYPE": {
                    "type": "string", "analyzer" : "key"
                },
                "WEATHER_NORMALIZED_SOURCE_EUI": {
                    "type": "double"
                },
                "NUMBER_OF_BUILDINGS": {
                    "type": "long"
                },
                "ASSET_ID": {
                    "type": "string"
                }
            }
        }
    }
}'

{"acknowledged":true}

$ curl -X PUT 'http://localhost:9200/philly_energy/_alias/pe'

{"acknowledged":true}

Load the data assuming you downloaded the data into /tmp/pe.dat.
$ curl -s -XPOST localhost:9200/_bulk --data-binary @/tmp/pe.dat

{"took":105,"errors":false,"items":[{"create":{"_index":"philly_energy","_type":"building","_id":"jZD9hwX7TEm_VWV3G2UDfQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"d5RKm33gTKaXUuFv52xpTw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TresrqkFS3K-qvWBbCYAqw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"migmCgp3RYKhTUbi9Po7tA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"yC8MolceR-mTN3C2cZqjbw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"lSqQrAiPTk6eeG-RrAnu9w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9grAtNzcTgO6sPuZ7H6PNw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pk58AXSvSgGqhuzeUH2fuA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"NI9GEFHERb6OgGXRhltPzA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"fQJaKz7hQCuyzfukSQ0mAA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"kA0E-4SiSDGJTkmMGRZJKw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"aDc_xQgARhq5QyuHg6aXVw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"7ryDa-GfSiqD7mXPwloh0w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YMJBo-tkRXKriXxvA7FP8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uJu7XmiqTdmJOxRlL11NYA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"vd6ZZPZyQFSTGnVsl8QnPw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hw5lKn7hRr26MAigUucH6A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DRuB3YDaTwW0thDkJSNGFg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"a4W2GMyoRd-3pvy07KXmJA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"BpKphzsyS4a5-DvjHNy52w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QVm2ImUYRj2_YSfaAr6wBg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"-A4ruNTyT2CTXgfi308ZKg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"JTQTngqjTEGonKG3pHJI7A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"PZ86P-laS1yioPux2_OJ-g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"z3rv4vobQlKTGG4JofNLKQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6JyGiHkKR3uQPGeijzvs9w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jA75x06_QpiS9KHNhemHmg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"OPecoX-pRXeYah8gbuyACQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"eaT_35LcTM2f6wgTN52pAQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rN4969B-R4q9qZddjBXFrw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6KCend1IQUKLlmvYtW_peA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6rUa1ohWRYuNHTqh96Xblg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hONw88VHQjKZR_ureQFVqg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"w6o5u4_QTYGGDOzbPoQUrA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cYtHoa0qR2GbwYnBrluL5g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pjQ6CYfAQXmwshfNB8vm0Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"91202-fyQXikkQ0csnfZQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"4KZpUUfRR6WjGi4bIhzbww","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"p_Pfm_ktT0mC6Aa9V74IiA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hKku4TjqQkabWMQILe8K5Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Ssokym6jTWe69bv2DOlVsQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hKCUoUKIQci6TwRObaowbQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"tjJgRMBWTN6te8fnmySqvQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xzx0rpiATQ64BGJrQ2A0Lw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sRpBZ4fWRgqkuuT6smjV0Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"KqM5c585RvOgHhkDrgspgg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TFo0lN4xQ8-cDiCECxTzNQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"utt89kHSSbSW4M6Jrakb2w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cdyuVZqgTuCTv-HtFZVhXg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YK0RG3rmRp6Bc78FGj-Jww","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cLOl4OyxTJO-1zJWKUTBYQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jI5zMLXSQaykXXc_DunjZA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"CnsRzzZzThiNILSQaaEQgQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"nHP4dlImSviSyK5sa1VCWA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"WPP_yIlVR-Kw7yMjhOJ37w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0F9bTwLFQPSGr8Vy4V96vA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"CCXGGBx1TYmG-_TP4sIp_A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6A1TBU28Ri22n7rdnyRbSw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"c-MpTEiXT0-NhTfwGHtQiw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Hi-UWbSCSxKVly0fFWr8rw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xNScIlGCQg2HMDDKNemoqw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qNqHfjPMSzuuRKE30q_GWw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sDWOMEUsSja65dnLwhIOcQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hM5b4Z1GQ7qbaCamQ22ssA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TCEfbU1aRzelgAR-0hlusA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"E_2T4_-1Q1WxcQiLc0xnhw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"c1-ZvLSTTAGowxi_vAYDFw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"RjaCYofqQISbwEH6PmeosQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"x4q5IpsaT8imMKHMniZC8A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cN2KU0F5TS-GL7hL-wszWw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YAmmp30IR8yJpIXRPlecaA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"-4I8YGPnQoaQrJLsN51G3Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"E7rNyxQKS6OKjdxx2THmVg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"3KZ4SrkgQ-qsoA4t8LsPyQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6Q4XYRtkTz6mBAAh932nUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"VfycIKg-R-WqIljhxU8Q4Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"oqKOn4yiSDKg_yeAgz1LWg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"65lT1L0PR7u2sHl_PFOhGw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"O1c1vQVOSd2Abm9MiGokZQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"roFhfV7YSxS0NLzS1l9M8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"GrcxXeuiSQudjqDOhiWpZA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qs37-dPJS-ysXRQO76d0Sg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qgHmJD1ISTCa0P49W6flUg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ifhNYW1xRWOVfD_IiajrCA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"J1e2DEgsRaidLOh0Ut_dEg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"fyXTWtZzTqyBMf5ox60OFQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_S2IlZpaQIqehqMTFsAlFw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"SOetHn-FTRumd50uc93aPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"OYcErWUTRZCjzhWWmUCOyQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9Bq9P3HJQjqmKwOpCY6k4A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"11nQwGkXTHGySGmJN01Lyw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"e6zRCLY7Tzqgf4gitrgo6g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"WybvumB5Ti6gHMcqgH4I2A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"FvvI5DEFQ-ysrug91t1XUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YKZFeIj-Q2S0prEqSIYW1A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DQRy9ASlRmeyOSJ5-j0M9g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DVBsPSiPQ0GVlMdV2FOhGg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Resw5iiDT8qGguZqoYyD8w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rBakQefjRz6GBEjzJMFh2A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_aDOYuv9Rz2igVCM2S4JPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ocXtB3tsTx-g0taaWBDkIQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uLphu9G5RhGxdkCb9uCJCw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6tX93bqWRzay6UPgewsNPA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"f0o6xpz-SUeSACRHU9bHGw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"KO6nkG9NTF6rpNU-aeLc6Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"aux8rN5XRFurU7SScc6Fmg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hqNcF8eMSoOj74gkVApGUg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rf_47xQ1TuibahTcgFkDGg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"l0qxz9UvTLWqQKyKhgZOQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QaWx3xw7SDWFEmf06cMRAw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"4iHpZ9C3RnCsbYFbjidalA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xthI877oR0ycdqQwFMRjVA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"92B5V3srQKmb0Eb0QKY7Pw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0fskFrTJSBOAP2e6af_LUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"JoduknymQG2TJxB5OX5d6g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"X0OfWnmUSca0o4tNzN_9tQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"i3ZtfDQpTKitSA41YG8YmA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"7BJaQp54Sd-O1wfe_mshDQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hNwd8xJYRnSTXQtlyz4kgA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"8XtuEdhRQtuoRuZos6XysQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"nGdk4D69TZqb0np4eDuj1g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Hb0sbflFQt-Esqk2nwkbrA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rhYP4lfATaWahoEkFfl8kQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"iEmRrY4NTm6IKEnHlU8EJg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jxKxFSZHRmOo7Qx_hjzSPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jRdfK2WAQnm22YvwaDakrA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"eQqq1X0RTnqS7F2lzib6AQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Yx2dRTz7TtixR1NA-OHQGA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DptHnqDBQlOPRKEsPv_p8g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"kph_4zu0QpWBOhvAKrojzA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"K5CaASmoS7i1l6StF0psJA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"tiA1ni7-REycfcz1S7gADw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Y8yUKpHiSIGx-IysVrADmQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Z_beIQcdTAS61msM0fC_2Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"4n0_w-S6SsG4jVhhLXQwPw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9ZfZAH4RQFKL2Rnabh1yIA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"oBkIPW8WS1-bRNM4VgJLnA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gKirGMG3TpOT69hf2SiQoQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"HkQmDAJXRHqk5HxVrzIK2w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"1qSA8gVpQEOi___2Mb0nyA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0bIl8CahSBq27jKw1_bJmQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sw-2RmEuRAqKr1KtQIvO6Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uvxKk_I8Qv-H5sTeyM4kDQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"B4Mv48X2TNupNDJDiNqcLA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"MGl7Ak5ORnGQ5JoUONZM2w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_gxF0cGKRLKVaM5-uwgQzw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"P_HVTiibRqKqVivwh9clBg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"X9_5Rk-ORDaBI85J84m4Eg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QnlEcw1QT6KmNe7BgWFwZA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"woosPLYsS3y8XqMleGIp5Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TZvXO0GRTraqMIBJTKGtDA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"GoE6a0wmReKBSy1NaXXmFg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"fSuPoF-ASiaSB56SDdqNQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QNHfLgBaT5i0HTz_4VWm7Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gQPGPssdRq6AeII8rT66RQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6Osl4kLgRcORqR1-FM3Xjw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"IVSDg-CJQ0-oE0Th0H_kHw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Epnd3nM3TNWk0IzHP4m1Yg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0CTj1JwUTo2gfn41Q1o8JQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"HcBzhC8sQy-gmJZpZtUYnw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"vg688zdTRi2o1NjIQWBZWQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"3OzNDAcCRq2LHD2VMrLdYw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"o2N8q7WzR6CWfE4d2a7HVA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6KP9fwYoRViEp-fJF-1Prg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ZuS8qvPVTmSu5-a75E-T7Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sohp7XJjSxGIgrU1QlFlcw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sx8q8viXQfuVK8g_WDxi_g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YrfwcuKDSxqtwD1eGCkQgw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"RqAiXaADR2ilnf6fNIQzAA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Vo9dxHOYSM2Tz831m2CzQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6gO5kTwqRJ-HJ1rhCWIedw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9-nj7zeeTsKZt3qkWg6wvg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"8h2hVf6FQkylIVZDrUB6Aw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wpmj0g0kRdGI4SFQM8pzlQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"SgRTXoY-QTKk9AJOJ6-lXQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Nz92MErwTMye-N1OSZBGOw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Pk7d5DavTG2gqw__DRX9Qg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ltE0Y6M0T26lyLsrkzOKtA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"se_6642URFedZXcwOL-N8g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"UagLDdasR8GkNf2zT4kmgw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"-yAXB48sTUS2fgKslVaKBQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"olqwNMt8ROuoRknAucJNrw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"B1VvggxwRmWzYqL_JwAlWg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xPpkOTSTTVeULOW9pJ92BQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_7D8i7JZT5yfTqnPPEn65Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"bred3tbKSnSL8mtTTN4LPQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Rqg-RcolQiCKmUQtBUa7_w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Qv3oRyWsTLK_D2sekrMUDg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6LTHuQw4QWaSE8-fQEWe5g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"XdsMH3SCSBKgP3lfk2bF8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wIxPtuJLStCcjQleYv677g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"1L4sVmYuQMW2w4plBqJmdA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Iifs4sSpS_OLN9nDUb4mug","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jYXotFQjQjWSi_UoJwXT2A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gjXWttrzShyoUCnkZxuJyg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"aui2O1G_RoepIZcxL4Z5gg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"vLPk2-uKQ9SEJQH6kCokvQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gqGS9wRTTMOP4zr26CRLhQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pd2PeAauQlKVyhTUGEZcHg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"PYafcu8ZSvC7RXIi2t_78w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"8y5709ruS8K0avG0lf-D0g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"WwZE7zVIRIy31VpSPJ3F9w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"5uslg5_bSeWZ9p6zwpTM8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"etTu8ohZSxKWrdafLtf9Kg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"XqgetrO9QqWYMzYneAFQRA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Q2o2n2oVS1O4aESgoHbZXg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"IcDQHLIWTTuAC3UB1-o_-g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uUySsLSDSFCKkkrQWivXQw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"dWN310uJSiivq941kU62Rg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"zyzSlJJyS9yum5qGtqAe4A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YS9sur_FSoCif72Y2hRkBQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"OTxp13xJQwKEHrafda5_UQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9yHYftusRBSKwjohKBUtKg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"3LoCUz2VR0-loKN5v4HlOA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"atwGgu0zS9COzRYmvLqSZw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Bw2e4VuxSOCf0uleeRa_Wg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"V3aQXSorRcCccHh_wNnXAg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6SDlTDWLTXi4zA_I7zXH6A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"r2Up-WJ8SMm7OqGL6GEG1w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uGasV8qCSjGsup_Z40sIkQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"NY5TUCKITCaNtNaB03ekTw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gM6-Agk6RVGWflwkrQ_P4A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Glf8eSV6Qzy7Pg7y-v3cGA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qzr65awRSUuDwnd3JdTclw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"a4voWN4yT2Oz_FN15R9YxQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Wqh5IrivT_GGT4LD6zwSFA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"BiAQO4JJQvq5MolPSQHcUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wVf52zpJQb2fcZ79BB8YAQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"poZaeJCDQimsnYlZyxVW8A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"FtK6YKPJR8OWq7nJIPh_Bg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uPsJBTMPR2SY0GkRBdo3yA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jN-DM-_IQwuxWbyNgHPimw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DUifP8ZtQTu7FFrPSOLYag","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"1dYi9Yb0SxmZwq-TmEsrRg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"nOhf7I-cTyiYVMjjAFU3tA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xyL2deXbSsK79_vV2Q3Rew","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"D_VRktEKRGSJDBT1oOoOwg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"f_T_ZUGmSj-MEJYzEKYWmw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"s7mk8jVtQ_6oo4cIRqIzCA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"5I2Xveg0QdGOn0e67BxsJw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"RtZmKAxNRLigEP2CjJQEKQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"H7IorpHvSaaH812L9cFg7A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"41K2Ii8USdu_6U2bVf__OQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"JR7ROqm_QRagzXV2eAusgQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rn9VatTGRcywfnkeUYm9Nw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pBWm787ETv-C0yZyT-t6Jw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9AYn0oZoRhacIcSdKpySWw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"C2PBBP7lTDiPlrk1VH7pRw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ab1eowikTGSvj-gq0QXTRQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DN615cr9RUmAMqDKjEFL5Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"FfTWDNzFSXyUPKouGEx9lg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"x7LsVktqTAOW959G6XO-JQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"utqvXUwDQ3Giqmxha-CiaA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"mWj9dzjgRMGntA2VD6479w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wUmqIVi5QyO7I7IVCHTfSQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"k7iHOpK6TIq5FU1Fu0NPRg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"R2Gbz524R2eot9kw7t7z-A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"XOAqG86DTmSA5-3tapcyPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rs1G0QS6R5WZ6oaHM3s7qw","_version":1,"status":201}}]}

The old way to do facets in elastic search.  This is still very powerful but, the new way is even more powerful.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
  "query": {
    "match_all": {}
  },
  "facets": {
    "tags": {
      "terms": {
        "field": "PROPERTY_TYPE"
      }
    }
  },
  "size": 0
}'

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 259,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "facets" : {
    "tags" : {
      "_type" : "terms",
      "missing" : 0,
      "total" : 259,
      "other" : 21,
      "terms" : [ {
        "term" : "other - recreation",
        "count" : 80
      }, {
        "term" : "fire station",
        "count" : 50
      }, {
        "term" : "library",
        "count" : 46
      }, {
        "term" : "police station",
        "count" : 21
      }, {
        "term" : "repair services (vehicle, shoe, locksmith, etc.)",
        "count" : 11
      }, {
        "term" : "office",
        "count" : 8
      }, {
        "term" : "medical office",
        "count" : 7
      }, {
        "term" : "museum",
        "count" : 6
      }, {
        "term" : "prison/incarceration",
        "count" : 5
      }, {
        "term" : "other - public services",
        "count" : 4
      } ]
    }
  }
}

An example with a script
$ curl -X POST 'http://localhost:9200/pe/building/_search?pretty=true' -d '{
  "query": {
    "match_all": {}
  },
  "facets": {
    "tags": {
      "terms": {
        "field": "PROPERTY_TYPE",
        "script": "term == \u0027fire station\u0027 ? true : false"
      }
    }
  },
  "size": 0
}'

{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 259,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "facets" : {
    "tags" : {
      "_type" : "terms",
      "missing" : 0,
      "total" : 259,
      "other" : 209,
      "terms" : [ {
        "term" : "fire station",
        "count" : 50
      } ]
    }
  }
}


This is the new way to do a facet in Elasticsearch 1.0.0.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "by_type": {
      "terms": {
        "field": "PROPERTY_TYPE"
      }
    }
  },
  "size": 0
}'

{
  "took" : 6,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 259,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "by_type" : {
      "buckets" : [ {
        "key" : "other - recreation",
        "doc_count" : 80
      }, {
        "key" : "fire station",
        "doc_count" : 50
      }, {
        "key" : "library",
        "doc_count" : 46
      }, {
        "key" : "police station",
        "doc_count" : 21
      }, {
        "key" : "repair services (vehicle, shoe, locksmith, etc.)",
        "doc_count" : 11
      }, {
        "key" : "medical office",
        "doc_count" : 8
      }, {
        "key" : "office",
        "doc_count" : 8
      }, {
        "key" : "museum",
        "doc_count" : 6
      }, {
        "key" : "prison/incarceration",
        "doc_count" : 4
      }, {
        "key" : "other",
        "doc_count" : 3
      } ]
    }
  }
}

Here is a histogram with groups of every 50 years and within those 50 year buckets it count how many buildings of different types were completed.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "by_year": {
      "histogram": {
        "field": "YEAR_BUILT",
        "interval": 50
      },
      "aggs": {
        "by_type": {
          "terms": {
            "field": "PROPERTY_TYPE"
          }
        }
      }
    }
  },
  "size": 0
}'

{
  "took" : 79,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 259,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "by_year" : {
      "buckets" : [ {
        "key" : 1700,
        "doc_count" : 1,
        "by_type" : {
          "buckets" : [ {
            "key" : "other - recreation",
            "doc_count" : 1
          } ]
        }
      }, {
        "key" : 1750,
        "doc_count" : 1,
        "by_type" : {
          "buckets" : [ {
            "key" : "museum",
            "doc_count" : 1
          } ]
        }
      }, {
        "key" : 1800,
        "doc_count" : 1,
        "by_type" : {
          "buckets" : [ {
            "key" : "museum",
            "doc_count" : 1
          } ]
        }
      }, {
        "key" : 1850,
        "doc_count" : 6,
        "by_type" : {
          "buckets" : [ {
            "key" : "office",
            "doc_count" : 2
          }, {
            "key" : "fire station",
            "doc_count" : 1
          }, {
            "key" : "other - recreation",
            "doc_count" : 1
          }, {
            "key" : "police station",
            "doc_count" : 1
          }, {
            "key" : "prison/incarceration",
            "doc_count" : 1
          } ]
        }
      }, {
        "key" : 1900,
        "doc_count" : 92,
        "by_type" : {
          "buckets" : [ {
            "key" : "other - recreation",
            "doc_count" : 47
          }, {
            "key" : "library",
            "doc_count" : 19
          }, {
            "key" : "fire station",
            "doc_count" : 13
          }, {
            "key" : "museum",
            "doc_count" : 3
          }, {
            "key" : "police station",
            "doc_count" : 2
          }, {
            "key" : "courthouse",
            "doc_count" : 1
          }, {
            "key" : "ice/curling rink",
            "doc_count" : 1
          }, {
            "key" : "laboratory",
            "doc_count" : 1
          }, {
            "key" : "non-refrigerated warehouse",
            "doc_count" : 1
          }, {
            "key" : "office",
            "doc_count" : 1
          } ]
        }
      }, {
        "key" : 1950,
        "doc_count" : 150,
        "by_type" : {
          "buckets" : [ {
            "key" : "fire station",
            "doc_count" : 36
          }, {
            "key" : "other - recreation",
            "doc_count" : 29
          }, {
            "key" : "library",
            "doc_count" : 26
          }, {
            "key" : "police station",
            "doc_count" : 16
          }, {
            "key" : "repair services (vehicle, shoe, locksmith, etc.)",
            "doc_count" : 11
          }, {
            "key" : "medical office",
            "doc_count" : 8
          }, {
            "key" : "office",
            "doc_count" : 5
          }, {
            "key" : "other - public services",
            "doc_count" : 3
          }, {
            "key" : "prison/incarceration",
            "doc_count" : 3
          }, {
            "key" : "laboratory",
            "doc_count" : 2
          } ]
        }
      }, {
        "key" : 2000,
        "doc_count" : 6,
        "by_type" : {
          "buckets" : [ {
            "key" : "other - recreation",
            "doc_count" : 2
          }, {
            "key" : "police station",
            "doc_count" : 2
          }, {
            "key" : "library",
            "doc_count" : 1
          }, {
            "key" : "prison/incarceration",
            "doc_count" : 1
          } ]
        }
      } ]
    }
  }
}

Here I show the most floor space on average by building type.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "by_type": {
      "terms": {
        "field": "PROPERTY_TYPE",
        "size": 50,
        "order": {
          "area": "desc"
        }
      },
      "aggs": {
        "area": {
          "avg": {
            "field": "BLDG_FLOOR_AREA"
          }
        }
      }
    }
  },
  "size": 0
}'

{
  "took" : 20,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 259,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "by_type" : {
      "buckets" : [ {
        "key" : "office",
        "doc_count" : 8,
        "area" : {
          "value" : 595797.625
        }
      }, {
        "key" : "courthouse",
        "doc_count" : 2,
        "area" : {
          "value" : 425000.0
        }
      }, {
        "key" : "prison/incarceration",
        "doc_count" : 6,
        "area" : {
          "value" : 418769.1666666667
        }
      }, {
        "key" : "museum",
        "doc_count" : 6,
        "area" : {
          "value" : 144898.5
        }
      }, {
        "key" : "senior care community",
        "doc_count" : 3,
        "area" : {
          "value" : 124181.0
        }
      }, {
        "key" : "ice/curling rink",
        "doc_count" : 1,
        "area" : {
          "value" : 66000.0
        }
      }, {
        "key" : "laboratory",
        "doc_count" : 3,
        "area" : {
          "value" : 51233.333333333336
        }
      }, {
        "key" : "performing arts",
        "doc_count" : 1,
        "area" : {
          "value" : 48557.0
        }
      }, {
        "key" : "repair services (vehicle, shoe, locksmith, etc.)",
        "doc_count" : 11,
        "area" : {
          "value" : 46718.0
        }
      }, {
        "key" : "multifamily housing",
        "doc_count" : 1,
        "area" : {
          "value" : 33998.0
        }
      }, {
        "key" : "other",
        "doc_count" : 3,
        "area" : {
          "value" : 27911.0
        }
      }, {
        "key" : "medical office",
        "doc_count" : 8,
        "area" : {
          "value" : 24793.0
        }
      }, {
        "key" : "other - public services",
        "doc_count" : 4,
        "area" : {
          "value" : 23008.5
        }
      }, {
        "key" : "police station",
        "doc_count" : 21,
        "area" : {
          "value" : 22157.04761904762
        }
      }, {
        "key" : "residence hall/dormitory",
        "doc_count" : 1,
        "area" : {
          "value" : 20679.0
        }
      }, {
        "key" : "library",
        "doc_count" : 46,
        "area" : {
          "value" : 14417.95652173913
        }
      }, {
        "key" : "non-refrigerated warehouse",
        "doc_count" : 2,
        "area" : {
          "value" : 13228.0
        }
      }, {
        "key" : "other - recreation",
        "doc_count" : 80,
        "area" : {
          "value" : 12987.2
        }
      }, {
        "key" : "fire station",
        "doc_count" : 50,
        "area" : {
          "value" : 8340.54
        }
      }, {
        "key" : "",
        "doc_count" : 2,
        "area" : {
          "value" : null
        }
      } ]
    }
  }
}


Last I show how to query for what building type uses the most electric per square foot and also the average electric by square foot.  This will give us the highest cost per square foot to run and give use a nice baseline.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "by_type": {
      "terms": {
        "field": "PROPERTY_TYPE",
        "size": 50,
        "order": {
          "electric_by_area": "desc"
        }
      },
      "aggs": {
        "electric_by_area": {
          "avg": {
            "script": "doc[\u0027ELECTRIC_USE\u0027].value / doc[\u0027BLDG_FLOOR_AREA\u0027].value > 0?doc[\u0027BLDG_FLOOR_AREA\u0027].value : 1"
          }
        }
      }
    },
    "total_electric_by_area": {
      "avg": {
        "script": "doc[\u0027ELECTRIC_USE\u0027].value / doc[\u0027BLDG_FLOOR_AREA\u0027].value > 0?doc[\u0027BLDG_FLOOR_AREA\u0027].value : 1"
      }
    }
  },
  "size": 0
}'

{
  "took" : 10,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 259,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "total_electric_by_area" : {
      "value" : 50937.66795366795
    },
    "by_type" : {
      "buckets" : [ {
        "key" : "office",
        "doc_count" : 8,
        "electric_by_area" : {
          "value" : 595797.625
        }
      }, {
        "key" : "courthouse",
        "doc_count" : 2,
        "electric_by_area" : {
          "value" : 425000.0
        }
      }, {
        "key" : "prison/incarceration",
        "doc_count" : 6,
        "electric_by_area" : {
          "value" : 418769.1666666667
        }
      }, {
        "key" : "museum",
        "doc_count" : 6,
        "electric_by_area" : {
          "value" : 144898.5
        }
      }, {
        "key" : "senior care community",
        "doc_count" : 3,
        "electric_by_area" : {
          "value" : 124181.0
        }
      }, {
        "key" : "ice/curling rink",
        "doc_count" : 1,
        "electric_by_area" : {
          "value" : 66000.0
        }
      }, {
        "key" : "laboratory",
        "doc_count" : 3,
        "electric_by_area" : {
          "value" : 51233.333333333336
        }
      }, {
        "key" : "performing arts",
        "doc_count" : 1,
        "electric_by_area" : {
          "value" : 48557.0
        }
      }, {
        "key" : "repair services (vehicle, shoe, locksmith, etc.)",
        "doc_count" : 11,
        "electric_by_area" : {
          "value" : 46718.0
        }
      }, {
        "key" : "multifamily housing",
        "doc_count" : 1,
        "electric_by_area" : {
          "value" : 33998.0
        }
      }, {
        "key" : "other",
        "doc_count" : 3,
        "electric_by_area" : {
          "value" : 27911.0
        }
      }, {
        "key" : "medical office",
        "doc_count" : 8,
        "electric_by_area" : {
          "value" : 24793.0
        }
      }, {
        "key" : "other - public services",
        "doc_count" : 4,
        "electric_by_area" : {
          "value" : 23008.5
        }
      }, {
        "key" : "police station",
        "doc_count" : 21,
        "electric_by_area" : {
          "value" : 22157.04761904762
        }
      }, {
        "key" : "residence hall/dormitory",
        "doc_count" : 1,
        "electric_by_area" : {
          "value" : 20679.0
        }
      }, {
        "key" : "library",
        "doc_count" : 46,
        "electric_by_area" : {
          "value" : 14417.95652173913
        }
      }, {
        "key" : "non-refrigerated warehouse",
        "doc_count" : 2,
        "electric_by_area" : {
          "value" : 13228.0
        }
      }, {
        "key" : "other - recreation",
        "doc_count" : 80,
        "electric_by_area" : {
          "value" : 12987.2
        }
      }, {
        "key" : "fire station",
        "doc_count" : 50,
        "electric_by_area" : {
          "value" : 8340.54
        }
      }, {
        "key" : "",
        "doc_count" : 2,
        "electric_by_area" : {
          "value" : 0.0
        }
      } ]
    }
  }
}

Intro to Elasticsearch and New Features in version 1.0.x (Nested Documents)

These are the notes from my presentation: http://www.meetup.com/Elasticsearch-Philadelphia/events/164506092/

http://youtu.be/6Idi13rQx1o?t=57m5s

A big feature of Elasticsearch is the ability to roll up multiple Lucene documents into a single document while still being able to search the sub document and the rolled up document.

First we set-up our data model.  To do this we added the nested type to the tables we wanted to be able to search on as a sub document.  

$ curl -XPUT 'http://localhost:9200/park_maintenance' -d '
{
    "settings": {
        "number_of_shards": 5,
        "analysis": {
            "analyzer": {
                "key": {
                    "tokenizer": "keyword",
                    "filter": [
                        "lowercase"
                    ]
                }
            }
        }
    },
    "mappings": {
        "park": {
            "properties": {
                "park": {
                    "properties": {
                        "name": {
                            "type": "string"
                        },
                        "cost": {
                            "type": "long"
                        },
                        "tree": {
                            "type": "nested",
                            "properties": {
                                "height": {
                                    "type": "long"
                                },
                                "last_trimming": {
                                    "type": "date"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
'

{"acknowledged":true}

Now we set-up the data in the nested index.
$ curl -X PUT 'http://localhost:9200/park_maintenance/_alias/pm'

{"acknowledged":true}


$ curl -s -XPOST localhost:9200/_bulk -d '
{ "index" : { "_index" : "park_maintenance", "_type" : "park", "_id":"central"} }
{"park":{"name":"central","cost":"5000","tree":[{"height":100,"last_trimming":1375717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1395717333}]}}
{ "index" : { "_index" : "park_maintenance", "_type" : "park", "_id":"yellowstone"} }
{"park":{"name":"yellowstone","cost":"50000","tree":[{"height":99,"last_trimming":1395717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1375717333}]}}
'


{"took":12,"errors":false,"items":[{"index":{"_index":"park_maintenance","_type":"park","_id":"central","_version":1,"status":201}},{"index":{"_index":"park_maintenance","_type":"park","_id":"yellowstone","_version":1,"status":201}}]}


To be able to compare this to the default we set-up a second index without any mappings.
$ curl -s -XPOST localhost:9200/_bulk -d '
{ "index" : { "_index" : "park_maintenance_plain", "_type" : "park", "_id":"central"} }
{"park":{"name":"central","cost":"5000","tree":[{"height":100,"last_trimming":1375717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1395717333}]}}
{ "index" : { "_index" : "park_maintenance_plain", "_type" : "park", "_id":"yellowstone"} }
{"park":{"name":"yellowstone","cost":"50000","tree":[{"height":99,"last_trimming":1395717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1375717333}]}}
'

{"took":26,"errors":false,"items":[{"index":{"_index":"park_maintenance_plain","_type":"park","_id":"central","_version":2,"status":200}},{"index":{"_index":"park_maintenance_plain","_type":"park","_id":"yellowstone","_version":2,"status":200}}]}

$ curl -X PUT 'http://localhost:9200/park_maintenance_plain/_alias/pmp'


{"acknowledged":true}

First lets query for tree taller than 50 units and trimmed longer than 1384717333 ago. 

$ curl -X POST 'http://localhost:9200/park_maintenance_plain/park/_search?pretty=true' -d '{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "height": {
              "gte": 50
            }
          }
        },
        {
          "range": {
            "last_trimming": {
              "lt": 1384717333
            }
          }
        }
      ]
    }
  }
}'


{
  "took" : 66,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 1.4142135,
    "hits" : [ {
      "_index" : "park_maintenance_plain",
      "_type" : "park",
      "_id" : "central",
      "_score" : 1.4142135, "_source" : {"park":{"name":"central","cost":"5000","tree":[{"height":100,"last_trimming":1375717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1395717333}]}}
    }, {
      "_index" : "park_maintenance_plain",
      "_type" : "park",
      "_id" : "yellowstone",
      "_score" : 1.4142135, "_source" : {"park":{"name":"yellowstone","cost":"50000","tree":[{"height":99,"last_trimming":1395717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1375717333}]}}
    } ]
  }
}

We get too many parks back in this example.  This is due to all trees being grouped together.  There exists a tree taller than 50 units and there exists a tree that has not been trimmed since 1384717333.  But, they are not the same tree. 

To solve this problem we query our nested index.  This has the tree table marked as nested.  Which means that each tree will be queried on its own. 
$ curl -X POST 'http://localhost:9200/park_maintenance/park/_search?pretty=true' -d '{
"query": {
    "nested": {
      "path": "park.tree",
      "score_mode": "total",
      "query": {
        "bool": {
          "must": [
            {
              "range": {
                "height": {
                  "gte": 50
                }
              }
            },
            {
              "range": {
                "last_trimming": {
                  "lt": 1384717333
                }
              }
            }
          ]
        }
      }
    }
  }
}'

{
  "took" : 69,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.4142135,
    "hits" : [ {
      "_index" : "park_maintenance",
      "_type" : "park",
      "_id" : "central",
      "_score" : 1.4142135, "_source" : {"park":{"name":"central","cost":"5000","tree":[{"height":100,"last_trimming":1375717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1395717333}]}}
    } ]
  }
}

This time we only get the one park we should get.

This does not mean you cannot shoot yourself in the foot with nested. The following is a way to write a bad query using nested documents. The key is to remember to never repeat the same path more than once in your query.  Here there are two paths that have "park.tree".
$ curl -X POST 'http://localhost:9200/park_maintenance/park/_search?pretty=true' -d '{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "park.tree",
            "score_mode": "total",
            "query": {
              "range": {
                "height": {
                  "gte": 50
                }
              }
            }
          }
        },
        {
          "nested": {
            "path": "park.tree",
            "score_mode": "total",
            "query": {
              "range": {
                "last_trimming": {
                  "lt": 1384717333
                }
              }
            }
          }
        }
      ]
    }
  }
}'

{
  "took" : 8,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 2.1213202,
    "hits" : [ {
      "_index" : "park_maintenance",
      "_type" : "park",
      "_id" : "central",
      "_score" : 2.1213202, "_source" : {"park":{"name":"central","cost":"5000","tree":[{"height":100,"last_trimming":1375717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1395717333}]}}
    }, {
      "_index" : "park_maintenance",
      "_type" : "park",
      "_id" : "yellowstone",
      "_score" : 2.1213202, "_source" : {"park":{"name":"yellowstone","cost":"50000","tree":[{"height":99,"last_trimming":1395717333},{"height":70,"last_trimming":1385717333},{"height":10,"last_trimming":1375717333}]}}
    } ]
  }
}

As you can see this did gave the same result as without the nested.