{
    "info": {
        "author": "Tao Wen",
        "author_email": "taowen@gmail.com",
        "bugtrack_url": null,
        "classifiers": [
            "Development Status :: 4 - Beta",
            "Intended Audience :: Developers",
            "License :: OSI Approved :: Apache Software License",
            "Operating System :: OS Independent",
            "Programming Language :: Python",
            "Programming Language :: Python :: 2",
            "Programming Language :: Python :: 2.6",
            "Programming Language :: Python :: 2.7",
            "Programming Language :: Python :: Implementation :: CPython",
            "Programming Language :: Python :: Implementation :: PyPy"
        ],
        "description": "# Installation\n\npip install es-sql\n\n# Usage\n\n```\nimport es_sql\nes_sql.execute_sql(\n    'http://127.0.0.1:9200',\n    'SELECT COUNT(*) FROM your_index WHERE field=%(param)s',\n    arguments={'param': 'value'})\n```\n\narguments is optional if no %(param)s specified in the sql\n\n```es-sql``` command can also be used in commandline:\n\n```\ncat << EOF | es-sql http://127.0.0.1:9200\n    SELECT COUNT(*) FROM your_index\nEOF\n```\n\n# Syntax\n\nThe goal is to be able to express all the necessary elasticsearch DSL\n(used in the context of OLAP database, not full-text search engine) using SQL.\n\n## Query multiple index\n\n```FROM quote``` => ```quote*```\n\n```FROM index('quote')``` => ```quote```\n\n```FROM index('quote-%Y-%m-%d', '2015-01-01')``` => ```quote-2015-01-01```\n\n```FROM index('quote-%Y-%m-%d', '2015-01-01', '2015-01-03')``` => ```quote-2015-01-01,quote-2015-01-02,quote-2015-01-03```\n\n```FROM index('quote-%Y-%m-%d', now())```\n\n```FROM index('quote-%Y-%m-%d', now() - interval('2 DAYS'))```\n\n```FROM (index('quote') UNION index('symbol')) AS my_table``` => ```quote,symbol```\n\n```FROM (quote EXCEPT index('quote-2015-01-01')) AS my_table``` => ```quote*,-quote-2015-01-01```\n\n## Drill down by sub aggregation\n\nElasticsearch support sub aggregations. It can be expressed by multiple sql statements\n\n```\nWITH all_symbols AS (SELECT MAX(market_cap) AS max_all_times FROM symbol);\nWITH per_ipo_year AS (SELECT ipo_year, MAX(market_cap) AS max_this_year INSIDE all_symbols\n    GROUP BY ipo_year LIMIT 2);\n```\n\n```SELECT INSIDE``` can also be ```SELECT FROM```\n\n## Client side join\n\n```\nSELECT symbol FROM symbol WHERE sector='Finance' LIMIT 5;\nSAVE RESULT AS finance_symbols;\nSELECT MAX(adj_close) FROM quote\n    JOIN finance_symbols ON quote.symbol = finance_symbols.symbol;\nREMOVE RESULT finance_symbols;\n```\n\n## Server side join\n\nIt requires https://github.com/sirensolutions/siren-join\n\n```\nWITH finance_symbols AS (SELECT symbol FROM symbol WHERE sector='Finance' LIMIT 5);\nSELECT MAX(adj_close) FROM quote\n    JOIN finance_symbols ON quote.symbol = finance_symbols.symbol;\n```\n\n## Pagination\n\nTODO\n\n# Full text queries\n\n## Match Query\n\nTODO\n\n## Multi Match Query\n\nTODO\n\n## Common Terms Query\n\nTODO\n\n## Query String Query\n\nTODO\n\n## Simple Query String Query\n\nTODO\n\n# Term level queries\n\n## Term Query\n\n```\n{\n    \"term\" : { \"user\" : \"Kimchy\" }\n}\n```\n\n```\nWHERE user='Kimchy'\n```\n\nIf field is analyzed, term query actually means contains instead of fully equal\n\n## Terms Query\n\n```\n{\n    \"constant_score\" : {\n        \"filter\" : {\n            \"terms\" : { \"user\" : [\"kimchy\", \"elasticsearch\"]}\n        }\n    }\n}\n```\n```\nWHERE user IN ('kimchy', 'elasticsearch')\n```\n\nTerms look up will not be supported, use server side join instead.\n\n## Range Query\n\n```\n{\n    \"range\" : {\n        \"age\" : {\n            \"gte\" : 10,\n            \"lte\" : 20\n        }\n    }\n}\n```\n\n```\nWHERE age >= 10 AND age <=  20\n```\n\n```\n{\n    \"range\" : {\n        \"date\" : {\n            \"gte\" : \"now-1d\",\n            \"lt\" :  \"now\"\n        }\n    }\n}\n```\n\n```\nWHERE \"date\" >= now() - INTERVAL '1 day' AND \"date\" < now()\n```\n\n```\n{\n    \"range\" : {\n        \"date\" : {\n            \"gte\" : \"now-1d/d\",\n            \"lt\" :  \"now/d\"\n        }\n    }\n}\n```\n```\nWHERE \"date\" >= today() - interval('1 day') AND \"date\" < today()\n```\n```\n{\n    \"range\" : {\n        \"born\" : {\n            \"gte\": \"01/01/2012\",\n            \"lte\": \"2013\",\n            \"format\": \"dd/MM/yyyy||yyyy\"\n        }\n    }\n}\n```\n```\nWHERE born >= TIMESTAMP '2012-01-01 00:00:00' AND born <= TIMESTAMP '2013-01-01 00\uff1a00\uff1a00'\n```\nSuported datetime function are\n\n- datetime: TIMESTAMP '2012-01-01 00:00:00' can also be timestamp('2012-01-01 00:00:00')\n- day/hour/minute/second interval: INTERVAL '1 DAY' can also be interval('1 day')\n- current datetime: now()\n- current day: today()\n\nTODO: timezone\n\n## Exists Query\n\n```\n{\n    \"exists\" : { \"field\" : \"user\" }\n}\n```\n```\nWHERE user IS NOT NULL\n```\n\n## Prefix Query\n\nTODO\n\n## Wildcard Query\n\n```\n{\n    \"wildcard\" : { \"user\" : \"ki*y\" }\n}\n```\n```\nWHERE user LIKE 'ki%y'\n```\n\n```\n{\n    \"wildcard\" : { \"user\" : \"ki?y\" }\n}\n```\n```\nWHERE user LIKE 'ki_y'\n```\n\n## Regexp Query\n\nTODO\n\n## Fuzzy Query\n\nTODO\n\n## Type Query\n\n```\n{\n    \"type\" : {\n        \"value\" : \"my_type\"\n    }\n}\n```\n```\nWHERE _type='my_type'\n```\n\n## Ids Query\n\n```\n{\n    \"ids\" : {\n        \"values\" : [\"1\", \"4\", \"100\"]\n    }\n}\n```\n```\nWHERE _id IN ('1','4','100')\n```\n```\n{\n    \"ids\" : {\n        \"type\" : \"my_type\",\n        \"values\" : [\"1\", \"4\", \"100\"]\n    }\n}\n```\n```\nWHERE _type='my_type' AND _id IN ('1','4','100')\n```\n\n# Compound queries\n\n## Bool Query\n\n```\n{\n    \"bool\" : {\n        \"must\" : {\n            \"term\" : { \"user\" : \"kimchy\" }\n        },\n        \"filter\": {\n            \"term\" : { \"tag\" : \"tech\" }\n        },\n        \"must_not\" : {\n            \"range\" : {\n                \"age\" : { \"from\" : 10, \"to\" : 20 }\n            }\n        },\n        \"should\" : [\n            {\n                \"term\" : { \"tag\" : \"wow\" }\n            },\n            {\n                \"term\" : { \"tag\" : \"elasticsearch\" }\n            }\n        ]\n    }\n}\n```\n```\nWHERE user='kimchy' AND tag='tech' AND NOT (age >= 10 AND age < 20) AND (tag='wow' OR tag='elasticsearch')\n```\n\nTODO: minimum_should_match\n\n## Indicies Query\n\nTODO\n\n## Limit Query\n\nTODO\n\n# Joining queries\n\n## Nested Query\n\nTODO\n\n## Has Child Query\n\nTODO\n\n## Has Parent Query\n\nTODO\n\n# Geo queries\n\n## GeoShape Query\n\nTODO\n\n## Geo Bounding Box Query\n\nTODO\n\n## Geo Distance Query\n\nTODO\n\n## Geo Distance Range Query\n\nTODO\n\n## Geo Polygon Query\n\nTODO\n\n## Geohash Cell Query\n\nTODO\n\n# Specialized queries\n\n## Template Query\n\nTODO\n\n## Script Query\n\nTODO\n\n# Metric Aggregations\n\n## Avg Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"avg_grade\" : { \"avg\" : { \"field\" : \"grade\" } }\n    }\n}\n```\n```\nSELECT avg(grade) AS avg_grade\n```\n\nTODO: script, missing\n\n## Cardinality Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"author_count\" : {\n            \"cardinality\" : {\n                \"field\" : \"author\"\n            }\n        }\n    }\n}\n```\n```\nSELECT COUNT(DISTINCT author) AS author_count\n```\nTODO: Precision control, script, missing\n\n## Extended Stats Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"grades_stats\" : { \"extended_stats\" : { \"field\" : \"grade\" } }\n    }\n}\n```\nwill return\n```\n{\n    \"grade_stats\": {\n       \"count\": 9,\n       \"min\": 72,\n       \"max\": 99,\n       \"avg\": 86,\n       \"sum\": 774,\n       \"sum_of_squares\": 67028,\n       \"variance\": 51.55555555555556,\n       \"std_deviation\": 7.180219742846005,\n       \"std_deviation_bounds\": {\n        \"upper\": 100.36043948569201,\n        \"lower\": 71.63956051430799\n       }\n    }\n}\n```\n```\nSELECT SUM_OF_SQUARES(grade)\nSELECT VARIANCE(grade)\nSELECT STD_DEVIATION(grade)\nSELECT STD_DEVIATION_UPPER_BOUND(grade)\nSELECT STD_DEVIATION_LOWER_BOUND(grade)\n```\n\nTODO: script, missing\n\n## Geo Bounds Aggregation\n\nTODO\n\n## Geo Centroid Aggregation\n\nTODO\n\n## Max Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"max_price\" : { \"max\" : { \"field\" : \"price\" } }\n    }\n}\n```\n```\nSELECT MAC(price) AS max_price\n```\n\nTODO: script, missing\n\n## Min Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"min_price\" : { \"min\" : { \"field\" : \"price\" } }\n    }\n}\n```\n```\nSELECT MIN(price) AS min_price\n```\n\nTODO: script, missing\n\n## Percentiles Aggregation\n\nTODO\n\n## Percentile Ranks Aggregation\n\nTODO\n\n## Scripted Metric Aggregation\n\nTODO\n\n## Sum Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"intraday_return\" : { \"sum\" : { \"field\" : \"change\" } }\n    }\n}\n```\n```\nSELECT SUM(change) AS intraday_return\n```\n\nTODO: script, missing\n\n## Top hits Aggregation\n\nTODO\n\n## Value Count Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"grades_count\" : { \"value_count\" : { \"field\" : \"grade\" } }\n    }\n}\n```\n```\nSELECT COUNT(grade) AS grades_count\n```\n\nTODO: script\n\n# Bucket Aggregations\n\n## Children Aggregation\n\nTODO\n\n## Date Historgram Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"articles_over_time\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            }\n        }\n    }\n}\n```\n```\nGROUP BY DATE_TRUNC('month', \"date\") AS articles_over_time\n```\n```\n{\n    \"aggs\" : {\n        \"articles_over_time\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"1M\",\n                \"format\" : \"yyyy-MM-dd\"\n            }\n        }\n    }\n}\n```\n```\nGROUP BY TO_CHAR(DATE_TRUNC('month', \"date\"),'%Y-%m-%d') AS articles_over_time\n```\n\nTODO: 1.5 hours interval, timezone, offset, script, missing\n\n## Filter Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"red_products\" : {\n            \"filter\" : { \"term\": { \"color\": \"red\" } },\n            \"aggs\" : {\n                \"avg_price\" : { \"avg\" : { \"field\" : \"price\" } }\n            }\n        }\n    }\n}\n```\n```\nWITH all_products AS (SELECT COUNT(*) FROM product);\nSELECT AVG(price) AS avg_price FROM all_products WHERE color='red';\n```\n\nIf from table is not another named sql, the where condition will be translated to query instead of filter aggregation.\n\n## Filters Aggregation\n\n```\n{\n  \"aggs\" : {\n    \"messages\" : {\n      \"filters\" : {\n        \"other_bucket_key\": \"other_messages\",\n        \"filters\" : {\n          \"errors\" :   { \"term\" : { \"body\" : \"error\"   }},\n          \"warnings\" : { \"term\" : { \"body\" : \"warning\" }}\n        }\n      }\n    }\n  }\n}\n```\n```\nGROUP BY CASE WHEN body='error' THEN 'errors' WHEN body='warning' THEN 'warnings' ELSE 'other_messages' END AS messages\n```\n\n## Geo Distance Aggregation\n\nTODO\n\n## GeoHash grid Aggregation\n\nTODO\n\n## Histogram Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"prices\" : {\n            \"histogram\" : {\n                \"field\" : \"price\",\n                \"interval\" : 50\n            }\n        }\n    }\n}\n```\n```\nGROUP BY histogram(price, 50) AS prices\n```\n```\n{\n    \"aggs\" : {\n        \"prices\" : {\n            \"histogram\" : {\n                \"field\" : \"price\",\n                \"interval\" : 50,\n                \"order\" : { \"_key\" : \"desc\" }\n            }\n        }\n    }\n}\n```\n```\nGROUP BY histogram(price, 50) AS prices ORDER BY prices DESC\n```\n\nTODO: min_doc_count, offset, buckets_path, missing\n\n## IPv4 Range Aggregation\n\nTODO\n\n## Missing Aggregation\n\nTODO\n\n## Nested Aggregation\n\nTODO\n\n## Range Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"price_ranges\" : {\n            \"range\" : {\n                \"field\" : \"price\",\n                \"ranges\" : [\n                    { \"to\" : 50 },\n                    { \"from\" : 50, \"to\" : 100 },\n                    { \"from\" : 100 }\n                ]\n            }\n        }\n    }\n}\n```\n```\nGROUP BY CASE\n    WEHN price < 50 THEN 'range1'\n    WHEN price >= 50 AND price < 100 THEN 'range2'\n    WHEN price >= 100 THEN 'range3'\nEND AS price_ranges\n```\n\nTODO: script\n\n## Reverse nested Aggregation\n\nTODO\n\n## Sampler Aggregation\n\nTODO\n\n## Significant Terms Aggregation\n\nTODO\n\n## Terms Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"genders\" : {\n            \"terms\" : { \"field\" : \"gender\" }\n        }\n    }\n}\n```\n```\nGROUOP BY gender AS genders\n```\n```\n{\n    \"aggs\" : {\n        \"products\" : {\n            \"terms\" : {\n                \"field\" : \"product\",\n                \"size\" : 5\n            }\n        }\n    }\n}\n```\n```\nGROUP BY product AS products LIMIT 5\n```\n```\n{\n    \"aggs\" : {\n        \"genders\" : {\n            \"terms\" : {\n                \"field\" : \"gender\",\n                \"order\" : { \"_count\" : \"asc\" }\n            }\n        }\n    }\n}\n```\n```\nSELECT COUNT(*) AS c FROM xxx\n    GROUP BY gender AS genders ORDER BY c\n```\n```\n{\n    \"aggs\" : {\n        \"genders\" : {\n            \"terms\" : {\n                \"field\" : \"gender\",\n                \"order\" : { \"height_stats.std_deviation\" : \"desc\" }\n            },\n            \"aggs\" : {\n                \"height_stats\" : { \"extended_stats\" : { \"field\" : \"height\" } }\n            }\n        }\n    }\n}\n```\n```\nSELECT STD_DEVIATION(height) AS s FROM xxx\n    GROUP BY gender AS genders ORDER BY s\n```\n```\n{\n    \"aggs\" : {\n        \"countries\" : {\n            \"terms\" : {\n                \"field\" : \"address.country\",\n                \"order\" : { \"females>height_stats.avg\" : \"desc\" }\n            },\n            \"aggs\" : {\n                \"females\" : {\n                    \"filter\" : { \"term\" : { \"gender\" :  \"female\" }},\n                    \"aggs\" : {\n                        \"avg_height\" : { \"avg\" : { \"field\" : \"height\" }}\n                    }\n                }\n            }\n        }\n    }\n}\n```\n```\nWITH all AS (SELECT * FROM xxx GROUP BY address.country AS countries ORDER BY female_avg_height);\nSELECT AVG(height) AS female_avg_height FROM all WHERE gender='female'\n```\n\nTODO: document count error, min_doc_count, script, filtering, collect-to, missing\n\n# Pipeline Aggregations\n\n## Avg Bucket Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                }\n            }\n        },\n        \"avg_monthly_sales\": {\n            \"avg_bucket\": {\n                \"buckets_path\": \"sales_per_month>sales\"\n            }\n        }\n    }\n}\n```\n```\nWITH sales_per_month AS (SELECT month, SUM(price) AS sales FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month);\nSELECT AVG(sales) AS avg_monthly_sales FROM sales_per_month;\n```\n\nTODO: gap_policy\n\n## Derivative Aggregation\n\nFirst Order Derivative\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                },\n                \"sales_deriv\": {\n                    \"derivative\": {\n                        \"buckets_path\": \"sales\"\n                    }\n                }\n            }\n        }\n    }\n}\n```\n```\nSELECT month, SUM(price) AS sales, DERIVATIVE(sales) AS sales_deriv\n    FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month\n```\nSecond Order Derivative\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                },\n                \"sales_deriv\": {\n                    \"derivative\": {\n                        \"buckets_path\": \"sales\"\n                    }\n                },\n                \"sales_2nd_deriv\": {\n                    \"derivative\": {\n                        \"buckets_path\": \"sales_deriv\"\n                    }\n                }\n            }\n        }\n    }\n}\n```\n```\nSELECT month, SUM(price) AS sales, DERIVATIVE(sales) AS sales_deriv, DERIVATIVE(sales_deriv) AS sales_2nd_deriv\n    FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month\n```\n\nTODO: unit, gap_policy\n\n## Max Bucket Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                }\n            }\n        },\n        \"max_monthly_sales\": {\n            \"max_bucket\": {\n                \"buckets_path\": \"sales_per_month>sales\"\n            }\n        }\n    }\n}\n```\n```\nWITH sales_per_month AS (SELECT month, SUM(price) AS sales FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month);\nSELECT MAX(sales) AS max_monthly_sales FROM sales_per_month;\n```\n\nTODO: gap_policy\n\n## Min Bucket Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                }\n            }\n        },\n        \"min_monthly_sales\": {\n            \"min_bucket\": {\n                \"buckets_path\": \"sales_per_month>sales\"\n            }\n        }\n    }\n}\n```\n```\nWITH sales_per_month AS (SELECT month, SUM(price) AS sales FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month);\nSELECT MIN(sales) AS min_monthly_sales FROM sales_per_month;\n```\n\nTODO: gap_policy\n\n## Sum Bucket Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                }\n            }\n        },\n        \"sum_monthly_sales\": {\n            \"sum_bucket\": {\n                \"buckets_path\": \"sales_per_month>sales\"\n            }\n        }\n    }\n}\n```\n```\nWITH sales_per_month AS (SELECT month, SUM(price) AS sales FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month);\nSELECT SUM(sales) AS sum_monthly_sales FROM sales_per_month;\n```\n\nTODO: gap_policy\n\n## Stats Bucket Aggregation\n\nTODO\n\n## Extended Stats Bucket Aggregation\n\nTODO\n\n## Percentiles Bucket Aggregation\n\nTODO\n\n## Moving Average Aggregation\n\n```\n{\n    \"moving_avg\": {\n        \"buckets_path\": \"the_sum\",\n        \"model\": \"holt\",\n        \"window\": 5,\n        \"gap_policy\": \"insert_zero\",\n        \"settings\": {\n            \"alpha\": 0.8\n        }\n    }\n}\n```\n```\nSELECT moving_avg(the_sum, '{\"model\":\"holt\",\"window\":5,\"gap_policy\":\"insert_zero\",\"settings\":{\"alpha\":0.8}}')\n```\nCan also be\n```\nSELECT moving_avg(the_sum, model='holt', window=5, gap_policy='insert_zero', settings='{\"alpha\":0.8}')\n```\n\n## Cumulative Sum Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                },\n                \"cumulative_sales\": {\n                    \"cumulative_sum\": {\n                        \"buckets_path\": \"sales\"\n                    }\n                }\n            }\n        }\n    }\n}\n```\n```\nSELECT month, SUM(price) AS sales, CSUM(sales) AS cumulative_sales\n    FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month\n```\n\n## Bucket Script Aggregation\n\nTODO\n\n## Bucket Selector Aggregation\n\n```\n{\n    \"aggs\" : {\n        \"sales_per_month\" : {\n            \"date_histogram\" : {\n                \"field\" : \"date\",\n                \"interval\" : \"month\"\n            },\n            \"aggs\": {\n                \"total_sales\": {\n                    \"sum\": {\n                        \"field\": \"price\"\n                    }\n                }\n                \"sales_bucket_filter\": {\n                    \"bucket_selector\": {\n                        \"buckets_path\": {\n                          \"totalSales\": \"total_sales\"\n                        },\n                        \"script\": \"totalSales <= 50\"\n                    }\n                }\n            }\n        }\n    }\n}\n```\n```\nSELECT month, SUM(price) AS total_sales\n    FROM sale GROUP BY DATE_TRUNC('month', \"date\") AS month\n    HAVING total_sales <= 50\n```\n\nTODO: gap_policy\n\n## Serial Differencing Aggregation\n\n```\n{\n   \"aggs\": {\n      \"my_date_histo\": {\n         \"date_histogram\": {\n            \"field\": \"timestamp\",\n            \"interval\": \"day\"\n         },\n         \"aggs\": {\n            \"the_sum\": {\n               \"sum\": {\n                  \"field\": \"lemmings\"\n               }\n            },\n            \"thirtieth_difference\": {\n               \"serial_diff\": {\n                  \"buckets_path\": \"the_sum\",\n                  \"lag\" : 30\n               }\n            }\n         }\n      }\n   }\n}\n```\n```\nSELECT SUM(lemmings) AS the_sum, SERIAL_DIFF(the_sum, lag=30) AS thirtieth_difference FROM xxx\n    GROUP BY DATE_TRUNC('day', \"timestamp\") AS my_date_histo\n```",
        "description_content_type": null,
        "docs_url": null,
        "download_url": "UNKNOWN",
        "downloads": {
            "last_day": -1,
            "last_month": -1,
            "last_week": -1
        },
        "home_page": "https://github.com/taowen/es-monitor",
        "keywords": "sql elasticsearch es",
        "license": "Apache License, Version 2.0",
        "maintainer": null,
        "maintainer_email": null,
        "name": "es-sql",
        "package_url": "https://pypi.org/project/es-sql/",
        "platform": "UNKNOWN",
        "project_url": "https://pypi.org/project/es-sql/",
        "project_urls": {
            "Download": "UNKNOWN",
            "Homepage": "https://github.com/taowen/es-monitor"
        },
        "release_url": "https://pypi.org/project/es-sql/2.0.0/",
        "requires_dist": null,
        "requires_python": null,
        "summary": "Use sql to query from Elasticsearch",
        "version": "2.0.0"
    },
    "last_serial": 1983098,
    "releases": {
        "1.0.0": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "ecd1cc57d3755235815058607b82332d",
                    "sha256": "0a2698306f2791b2210913e35c28cef76b67105acd2417258384896a45f56c42"
                },
                "downloads": -1,
                "filename": "es_sql-1.0.0-py2-none-any.whl",
                "has_sig": false,
                "md5_digest": "ecd1cc57d3755235815058607b82332d",
                "packagetype": "bdist_wheel",
                "python_version": "2.7",
                "requires_python": null,
                "size": 80894,
                "upload_time": "2016-02-25T12:38:58",
                "url": "https://files.pythonhosted.org/packages/5e/95/37d375f891a62e47839e54a3ea3806d85137b41027427a4cb78805fe40dd/es_sql-1.0.0-py2-none-any.whl"
            },
            {
                "comment_text": "",
                "digests": {
                    "md5": "40c1030017781e57e22b2f00e78f1ca4",
                    "sha256": "50ff12d04445ab7e260bf5c5c6867adffafac34ba1ecd6c4f7f33a0cc7d98034"
                },
                "downloads": -1,
                "filename": "es-sql-1.0.0.tar.gz",
                "has_sig": false,
                "md5_digest": "40c1030017781e57e22b2f00e78f1ca4",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 59806,
                "upload_time": "2016-02-25T12:38:51",
                "url": "https://files.pythonhosted.org/packages/84/a6/05b5cd4b96852fba18b3dec8ae3fec951776bb780f41d999f0a226581d48/es-sql-1.0.0.tar.gz"
            }
        ],
        "1.0.1": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "422a07f1d220e90f64356dd3e98a6466",
                    "sha256": "fdd36baa0b8b365bb775b1283e65fe4a93c0534b06b0ce3995fe3dba4486e507"
                },
                "downloads": -1,
                "filename": "es_sql-1.0.1-py2-none-any.whl",
                "has_sig": false,
                "md5_digest": "422a07f1d220e90f64356dd3e98a6466",
                "packagetype": "bdist_wheel",
                "python_version": "2.7",
                "requires_python": null,
                "size": 81668,
                "upload_time": "2016-02-26T14:26:57",
                "url": "https://files.pythonhosted.org/packages/f7/0a/1c7ddd88fa89b10d84d9894f1788ce896e85ed35c125739522b1341b2cfd/es_sql-1.0.1-py2-none-any.whl"
            },
            {
                "comment_text": "",
                "digests": {
                    "md5": "5a3c1cfc807d76ce9b1845aa926cea51",
                    "sha256": "a795c6a1cc0f5b0af6f99af6babafeac2f18bfe8aca91ec836b39f1641075dfa"
                },
                "downloads": -1,
                "filename": "es-sql-1.0.1.tar.gz",
                "has_sig": false,
                "md5_digest": "5a3c1cfc807d76ce9b1845aa926cea51",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 60702,
                "upload_time": "2016-02-26T14:26:29",
                "url": "https://files.pythonhosted.org/packages/e8/56/acb8f9c85938c3b1bba0f1225148637659fe7f68555d70208ad1f3730901/es-sql-1.0.1.tar.gz"
            }
        ],
        "2.0.0": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "d83149adf1a93fdd68c4ee39fe7e3406",
                    "sha256": "0d84ae210e969efba3bc7736e0e9f8e7b86f524f6ad3561e7ce40aefcb3119e6"
                },
                "downloads": -1,
                "filename": "es_sql-2.0.0-py2-none-any.whl",
                "has_sig": false,
                "md5_digest": "d83149adf1a93fdd68c4ee39fe7e3406",
                "packagetype": "bdist_wheel",
                "python_version": "2.7",
                "requires_python": null,
                "size": 83134,
                "upload_time": "2016-03-01T00:56:33",
                "url": "https://files.pythonhosted.org/packages/14/1e/130f896cf648b20ca35b734b3192c824eccbbbaede158c97a732a8eda328/es_sql-2.0.0-py2-none-any.whl"
            },
            {
                "comment_text": "",
                "digests": {
                    "md5": "905fa93acb26d9e9ef39ae2336bc2193",
                    "sha256": "ddd7117b41691b8a38e483933bd4c0cf328505a69a0a28868068369de8bf3e08"
                },
                "downloads": -1,
                "filename": "es-sql-2.0.0.tar.gz",
                "has_sig": false,
                "md5_digest": "905fa93acb26d9e9ef39ae2336bc2193",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 61877,
                "upload_time": "2016-03-01T00:56:27",
                "url": "https://files.pythonhosted.org/packages/94/ad/fca4ec209fda933d15d40f6be4b776a334bdf0d60f8db722e73a956457f6/es-sql-2.0.0.tar.gz"
            }
        ]
    },
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "md5": "d83149adf1a93fdd68c4ee39fe7e3406",
                "sha256": "0d84ae210e969efba3bc7736e0e9f8e7b86f524f6ad3561e7ce40aefcb3119e6"
            },
            "downloads": -1,
            "filename": "es_sql-2.0.0-py2-none-any.whl",
            "has_sig": false,
            "md5_digest": "d83149adf1a93fdd68c4ee39fe7e3406",
            "packagetype": "bdist_wheel",
            "python_version": "2.7",
            "requires_python": null,
            "size": 83134,
            "upload_time": "2016-03-01T00:56:33",
            "url": "https://files.pythonhosted.org/packages/14/1e/130f896cf648b20ca35b734b3192c824eccbbbaede158c97a732a8eda328/es_sql-2.0.0-py2-none-any.whl"
        },
        {
            "comment_text": "",
            "digests": {
                "md5": "905fa93acb26d9e9ef39ae2336bc2193",
                "sha256": "ddd7117b41691b8a38e483933bd4c0cf328505a69a0a28868068369de8bf3e08"
            },
            "downloads": -1,
            "filename": "es-sql-2.0.0.tar.gz",
            "has_sig": false,
            "md5_digest": "905fa93acb26d9e9ef39ae2336bc2193",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 61877,
            "upload_time": "2016-03-01T00:56:27",
            "url": "https://files.pythonhosted.org/packages/94/ad/fca4ec209fda933d15d40f6be4b776a334bdf0d60f8db722e73a956457f6/es-sql-2.0.0.tar.gz"
        }
    ]
}