How to do double grouping in SQL query in ElasticSearch?

Question

PHP

The bottom line is, I want to pick up clusters of points from ES Sql with a query.
The request is like this.

SELECT quadkey, MIN(id) AS id, COUNT(id) AS count, AVG(lng) AS lng, AVG(lat) AS lat FROM spots WHERE
                           quadkey BETWEEN 0 AND 4398046511103 GROUP BY quadkey & 3298534883328

and ES swears at '&'


although the same query in SQL passes without problems.

the essence of the request is that I take points by the key of the tree, and group by key and mask, but ES does not want to group by mask.

are there any options to get around this? or to write such request in dsl style?

Answer 1

In short, I solved the problem like this

$topLeftLat = $_POST['topLeftLat'];
        $topLefiLng = $_POST['topLeftLng'];
        $bottomRightLat = $_POST['bottomRightLat'];
        $bottomRightLng = $_POST['bottomRightLng'];
        $zoom = $_POST['zoom'];

        $params['bool']['filter'][]['bool']['must']=
            [
                [
                    "exists" => [
                        "field" => "location"
                    ]
                ],
                [
                    "geo_bounding_box" => [
                        "location" => [
                            "top_left" => $topLeftLat.','.$topLefiLng,
                            "bottom_right" => $bottomRightLat.','.$bottomRightLng
                        ]
                    ]
                ]
            ];
        $query = ElasticObj::find();
        $p = ($zoom > 10 )? 10 : 8-(6-$zoom);
            $query->addAggregate('gridSplit', [
                'geotile_grid' => [
                    'bounds' => [
                        "top_left" => $topLeftLat.','.$topLefiLng,
                        "bottom_right" => $bottomRightLat.','.$bottomRightLng
                    ],
                    "field" => "location",
                    "precision" => $p,
                    "size" => 65535,
                    "shard_size" => 65535
                ],
                'aggs' => [
                    'gridCentroid' => [
                        'geo_centroid' => [
                            'field' => 'location'
                        ]
                    ]
                ]
            ]);
            $query->query($params);
            $dataProvider = new ActiveDataProvider([
                'query' => $query,
            ]);
            $aggregations = $dataProvider->getAggregations();