Elasticsearch DSL queries generated by SQL plugin

es-dsl.md

Query 1

SELECT cab_type,
       count(*)
FROM trips
GROUP BY cab_type

{
    "from": 0,
    "size": 0,
    "_source": {
        "includes": [
            "cab_type",
            "COUNT"
        ],
        "excludes": []
    },
    "fields": "cab_type",
    "aggregations": {
        "cab_type": {
            "terms": {
                "field": "cab_type",
                "size": 200
            },
            "aggregations": {
                "COUNT(*)": {
                    "value_count": {
                        "field": "_index"
                    }
                }
            }
        }
    }
}

Query 2

SELECT passenger_count,
       avg(total_amount)
FROM trips
GROUP BY passenger_count

{
    "from": 0,
    "size": 0,
    "_source": {
        "includes": [
            "passenger_count",
            "AVG"
        ],
        "excludes": []
    },
    "fields": "passenger_count",
    "aggregations": {
        "passenger_count": {
            "terms": {
                "field": "passenger_count",
                "size": 200
            },
            "aggregations": {
                "AVG(total_amount)": {
                    "avg": {
                        "field": "total_amount"
                    }
                }
            }
        }
    }
}

Query 3

SELECT passenger_count,
       count(*) trips
FROM trips
GROUP BY passenger_count,
         date_histogram(field='pickup_datetime',
                              'interval'='year',
                              'alias'='year')

{
    "from": 0,
    "size": 0,
    "_source": {
        "includes": [
            "passenger_count",
            "COUNT"
        ],
        "excludes": []
    },
    "fields": "passenger_count",
    "aggregations": {
        "passenger_count": {
            "terms": {
                "field": "passenger_count",
                "size": 200
            },
            "aggregations": {
                "year": {
                    "date_histogram": {
                        "field": "pickup_datetime",
                        "interval": "year",
                        "format": "yyyy-MM-dd HH:mm:ss"
                    },
                    "aggregations": {
                        "trips": {
                            "value_count": {
                                "field": "_index"
                            }
                        }
                    }
                }
            }
        }
    }
}