/select request for the pivot table

[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[] | FilterGroupObject
    },
    "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. Contains the following properties:
  • aggs – Object. Query aggregations. The part of the query that specifies which data should be aggregated and how. Contains the following properties:
    • values – Array of objects. Values to aggregate. Fields with at least one supported aggregation defined in the schema can be selected for the query as values. Each object in the array has the following properties:
      • field – Field Object. The field selected as a measure.
      • func – String. The 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", or a custom aggregation. Note: for the fields of the "number" type, Flexmonster Pivot supports built-in front-end aggregations.
    • by – Object. Fields by which the data should be aggregated:
      • rows – Array of Field Objects. Fields in rows.
      • cols – Array of Field Objects. Fields in columns.
  • filter (optional) – Array of Filter Objects|Filter Group Object. Query filters. The part of a query that specifies which filters should be applied to the data.
    If the server does not support multilevel hierarchies (i.e., the filters.advanced property is set to false), the filter’s structure is an array of Filter Objects.
    If multilevel hierarchies are supported, the filter can be:
    • An array of Filter Objects – when the version sent in the /handshake response is 2.8.5 or /handshake is not implemented.
    • The Filter Group Object – when the version sent in the /handshake response is 2.8.22 or later.
  • page – Number. The 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 of objects. Aggregated data. Each object in the array has the following properties:
  • values – Object. Numeric values that are calculated for a specific tuple.
    • (uniqueName) – Object. The field’s unique name.
      • (func) – Number. The 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. Note: totals for each field should be included in the response even if they are disabled on the client side.
• page (optional) – Number. The current page number. Starts from 0.
• pageTotal (optional) – Number. The 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