[starting from version: 2.8]

A request for data.

Request

{
    "type": "select"
    "index": string,
    "query": {
        "aggs": {
            "values"[]: {
                "field": FieldObject,
                "func": string
            },
            "by": {
                "rows"[]:FieldObject,
                "cols"[]: FieldObject
            }
        },
        "filter"[]: FilterObject
    },
    "page": number
}

The request has the following parameters:

• type — String. The type of the request. In this case, it is "select".
• index – String. The dataset identifier.
• query – Object. A query object.
  • aggs – Object. Query aggregations. The part of the query that specifies which data should be aggregated and how.
    • values – Array. Values to aggregate. Fields that have at least one supported aggregation defined in the schema can be selected for the query as values.
      • field – Field Object.
      • func – String. Aggregation function name. For each field, the list of supported aggregations is defined in the response to the /fields request. Supported values may include: "sum", "count", "distinctcount", "average", "median", "product", "min", "max", "stdevp", "stdevs", "none". Note: for the fields of the "number" type, Flexmonster Pivot supports built-in front-end aggregations.
    • by – Object. Fields to aggregate by.
      • rows – Array of Field Objects. Fields in rows.
      • cols – Array of Field Objects. Fields in columns.
  • filter (optional) – Array of Filter Objects. Query filters. The part of a query that specifies which filters should be applied. Each field with a filter is represented by a separate object.
  • page – Number. A page number. It can be used to load data by parts. If a response contains the pageTotal parameter, additional requests will be performed to load the remaining pages. Starts from 0.

Response

{
    "aggs"[]: {
        "values": {
            (uniqueName): {
                (func): number
            }
        },
        "keys": {
            (uniqueName): string | number
        }
    },
    "page": number,
    "pageTotal": number
} 

The response has the following parameters:

• aggs – Array. Aggregated data.
  • values – Object. Numeric values that are calculated for a specific tuple.
    • (uniqueName) – Object. Field’s unique name.
      • (func) – Number. Result of the calculation, where (func) is an aggregation function.
  • keys (optional) – Object. Field’s keys that describe a specific tuple. In case it is not defined, values are treated as totals.
    • (uniqueName) – String|Number. Field’s member, where (uniqueName) is a field’s unique name.
• page (optional) – Number. Current page number. Starts from 0.
• pageTotal (optional) – Number. Total number of pages. It can be used to load members by parts.

Examples

1. Example with one value
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "aggs": {
               "values": [{
                   "func": "sum",
                   "field": {
                       "uniqueName": "price"
                   }
               }]
           }
       },
       "page": 0
   }

   Response:

   {
       "aggs": [{
           "values": {
               "price": {
                   "sum": 123
               }
           }
       }]
   }
   

2. Example with two values
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "aggs": {
               "values": [{
                   "func": "sum", 
                   "field": {
                       "uniqueName": "price"
                   }
               }, {
                   "func": "sum", 
                   "field": {
                       "uniqueName": "quantity"
                   }
               }]
           }
       },
       "page": 0
   }
   

   Response:

   {
       "aggs": [{
           "values": {
               "price": {
                   "sum": 123
               },
               "quantity": {
                   "sum": 5
               }
           }
       }]
   }
   

3. Example with a field in rows
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "aggs": {
               "values": [{
                   "func": "sum", 
                   "field": {
                       "uniqueName": "price"
                   }
               }],
               "by": {
                   "rows": [{
                       "uniqueName": "city"
                   }]
               }
           }
       },
       "page": 0
   }
   

   Response:

   {
       "aggs": [{
           "values": {
               "price": {
                   "sum": 123
               }
           }
       }, {
           "keys": {
               "city": "Toronto"
           },
           "values": {
               "price": {
                   "sum": 100
               }
           }
       }, {
           "keys": {
               "city": "New York"
           },
           "values": {
               "price": {
                   "sum": 23
               }
           }
       }]
   }
   

4. Example with fields in rows and columns
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "aggs": {
               "values": [{
                   "func": "sum", 
                   "field": {
                       "uniqueName": "price"
                   }
               }],
               "by": {
                   "rows": [{
                       "uniqueName": "city"
                   }],
                   "cols": [{
                       "uniqueName": "color"
                   }]
               }
           }
       },
       "page": 0
   }
   

   Response:

   {
       "aggs": [{
           "values": {
               "price": {
                   "sum": 48
               }
           }
       }, {
           "keys": {
               "city": "New York"
           },
           "values": {
               "price": {
                   "sum": 20
               }
           }
       }, {
           "keys": {
               "city": "Toronto"
           },
           "values": {
               "price": {
                   "sum": 28
               }
           }
       }, {
           "keys": {
               "color": "blue"
           },
           "values": {
               "price": {
                   "sum": 38
               }
           }
       }, {
           "keys": {
               "color": "red"
           },
           "values": {
               "price": {
                   "sum": 10
               }
           }
       }, {
           "keys": {
               "city": "New York",
               "color": "blue"
           },
           "values": {
               "price": {
                   "sum": 20
               }
           }
       }, {
           "keys": {
               "city": "Toronto",
               "color": "blue"
           },
           "values": {
               "price": {
                   "sum": 18
               }
           }
       }, {
           "keys": {
               "city": "Toronto",
               "color": "red"
           },
           "values": {
               "price": {
                   "sum": 10
               }
           }
       }]
   }
   

5. Example with exclude members filter
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "filter": [{
               "field": {
                   "uniqueName": "city"
               },
               "exclude": [
                   {
                       "member": "New York"
                   },
                   {
                       "member": "Montreal"
                   }
               ]
           }],
           "aggs": {
               "values": [{
                   "func": "sum",
                   "field": {
                       "uniqueName": "price"
                   }
               }],
               "by": {
                   "rows": [{
                       "uniqueName": "city"
                   }]
               }
           }
       }
   } 

   Response:
   Format is the same as above.
6. Example with include/exclude members filter on several fields
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "filter": [{
                   "field": {
                       "uniqueName": "color"
                   },
                   "include": [
                       {
                           "member": "blue"
                       }
                   ]
               },
               {
                   "field": {
                       "uniqueName": "city"
                   },
                   "exclude": [
                       {
                           "member": "New York"
                       },
                       {
                           "member": "Montreal"
                       }
                   ]
           }],
           "aggs": {
               "values": [{
                   "func": "sum",
                   "field": {
                       "uniqueName": "price"
                   }
               }],
               "by": {
                   "rows": [{
                       "uniqueName": "city"
                   }]
               }
           }
       }
   }
   

   Response:
   Format is the same as above.
7. Example with a conditional filter on members for string field
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "filter": [{
               "field": {
                   "uniqueName": "city"
               },
               "query": {
                   "begin": "toro"
               }
           }],
           "aggs": {
               "values": [{
                   "func": "sum",
                   "field": {
                       "uniqueName": "price"
                   }
               }],
               "by": {
                   "rows": [{
                       "uniqueName": "city"
                   }]
               }
           }
       }
   }
   

   Response:
   Format is the same as above.
8. Example with a conditional filter on members for number field
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "filter": [{
               "field": {
                   "uniqueName": "quantity"
               },
               "query": {
                   "greater": 2
               }
           }],
           "aggs": {
               "values": [{
                   "func": "sum",
                   "field": {
                       "uniqueName": "price"
                   }
               }],
               "by": {
                   "rows": [{
                       "uniqueName": "quantity"
                   }]
               }
           }
       }
   }
   

   Response:
   Format is the same as above.
9. Example with a conditional filter on dates for date field
   Request:

   {
       "index": "data-set-123",
       "type": "select",
       "query": {
           "filter": [{
               "field": {
                   "field": "order_date"
               },
               "query": {
                   "between": [1564610400000, 1564696799999]
               }
           }],
           "aggs": {
               "values": [{
                   "func": "sum",
                   "field": {
                       "field": "price"
                   }
               }],
               "by": {
                   "rows": [{
                       "field": "order_date"
                   }]
               }
           }
       }
    }
   

   Response:
   Format is the same as above.
10.  Example with a conditional filter on values
    Request:

    {
        "index": "data-set-123",
        "type": "select",
        "query": {
            "filter": [{
                "field": {
                    "uniqueName": "city"
                },
                "query": {
                    "top": 3
                },
                "value": {
                    "func": "sum",
                    "field": {
                        "uniqueName": "price"
                    }
                }
            }],
            "aggs": {
                "values": [{
                    "func": "sum",
                    "field": {
                        "uniqueName": "price"
                    }
                }],
                "by": {
                    "rows": [{
                        "uniqueName": "city"
                    }]
                }
            }
        }
    }
    

    Response:
    Format is the same as above.

See also

/handshake request
/fields request
/members request
/select request for flat table
/select request for drill-through view