[starting from version: 2.8]
A request for data.
{ "type": "select" "index": string, "querytype": 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.querytype
– String. Allows distinguishing expand and drill-down requests from other select requests. The querytype
has one of the following values: "select"
, "expand"
, or "drillDown"
.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
– FieldObject. 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 FieldObjects. Fields in rows.cols
– Array of FieldObjects. Fields in columns. filter
optional – Array of FilterObjects | FilterGroupObject. Query filters. The part of a query that specifies which filters should be applied to the data.false
), the filter’s structure is an array of FilterObjects.2.8.5
or /handshake is not implemented.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
.{
"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. {Response:
"index": "data-set-123",
"type": "select",
"query": {
"aggs": {
"values": [{
"func": "sum",
"field": {
"uniqueName": "price"
}
}]
}
},
"page": 0
}
{
"aggs": [{
"values": {
"price": {
"sum": 123
}
}
}]
}
{Response:
"index": "data-set-123",
"type": "select",
"query": {
"aggs": {
"values": [{
"func": "sum",
"field": {
"uniqueName": "price"
}
}, {
"func": "sum",
"field": {
"uniqueName": "quantity"
}
}]
}
},
"page": 0
}
{
"aggs": [{
"values": {
"price": {
"sum": 123
},
"quantity": {
"sum": 5
}
}
}]
}
{Response:
"index": "data-set-123",
"type": "select",
"query": {
"aggs": {
"values": [{
"func": "sum",
"field": {
"uniqueName": "price"
}
}],
"by": {
"rows": [{
"uniqueName": "city"
}]
}
}
},
"page": 0
}
{
"aggs": [{
"values": {
"price": {
"sum": 123
}
}
}, {
"keys": {
"city": "Toronto"
},
"values": {
"price": {
"sum": 100
}
}
}, {
"keys": {
"city": "New York"
},
"values": {
"price": {
"sum": 23
}
}
}]
}
{Response:
"index": "data-set-123",
"type": "select",
"query": {
"aggs": {
"values": [{
"func": "sum",
"field": {
"uniqueName": "price"
}
}],
"by": {
"rows": [{
"uniqueName": "city"
}],
"cols": [{
"uniqueName": "color"
}]
}
}
},
"page": 0
}
{
"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
}
}
}]
}
{Response:
"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:
"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:
"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:
"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:
"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:
"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"
}]
}
}
}
}
/handshake request
/fields request
/members request
/select request for flat table
/select request for drill-through view