Need a special offer?Find out if your project fits.
+
All documentation
API reference
  • API Reference for older versions
  • /select request for the pivot table

    [starting from version: 2.8]

    A request for data.

    Request

    {
      "type": string,
      "index": string,
      "querytype": string,
      "query": {
        "aggs": {
          "values": ValueObject[],
          "by": {
            "rows": FieldObject[],
            "cols": FieldObject[]
          }
        },
        "filter": FilterObject[] | FilterGroupObject
      },
      "page": number,
      "pageToken": string
    }
    Property/Type Description
    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.
    query.aggs
    Object
    Query aggregations. The part of the query that specifies which data should be aggregated and how.
    query.aggs.values
    ValueObject[]
    Values to aggregate. Fields with at least one supported aggregation defined in the schema can be selected for the query as values.
    query.aggs.by
    Object
    Fields by which the data should be aggregated.
    query.aggs.by.rows
    FieldObject[]
    Fields in rows.
    query.aggs.by.cols
    FieldObject[]
    Fields in columns.
    query.filter
    FilterObject[] | FilterGroupObject
    optional 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., filters.advanced is set to false), the filter's structure is an array of FilterObjects.
    If multilevel hierarchies are supported, the filter can be:
    • An array of FilterObjects — when your server implements the custom data source API version between 2.8.5 and 2.8.21.
    • The FilterGroupObject — when your server implements the custom data source API version 2.8.22 or later.
    See how to check your version of the custom data source API.
    page
    Number
    Used to load data by pages when their total number can be predicted.
    Indicates which page the server should send in the next response. The page's value starts from 0.
    This property is always present in the initial request and will be included in the next /select requests while the server responses contain the pageTotal and page properties.
    Ignore this property if you don't want to load data by pages.
    pageToken
    String
    Used to load data by pages when their total number cannot be predicted.
    Indicates which page the server should send in the next response. The pageToken's value is equal to the nextPageToken sent in the previous server response.
    This property isn't present in the initial request but will be included in the next /select requests while the server responses contain the nextPageToken property.

    Response

    {
      "aggs": AggregatedDataObject[],
      "pageTotal": number,
      "page": number,
      "nextPageToken": string
    } 
    Property/Type Description
    aggs
    AggregatedDataObject[]
    Aggregated data.
    pageTotal
    Number
    optional The total number of pages. Used to load data by pages when their total number can be predicted.
    Must be used with the page property. To load the next pages, the component will continue sending the /select requests while the page is less than the pageTotal.
    If the number of pages cannot be predicted — use the nextPageToken property.
    page
    Number
    optional The page number requested by the component in the page property. Used to load data by pages when their total number can be predicted.
    Must be used with the pageTotal property. To load the next pages, the component will continue sending the /select requests while the page is less than the pageTotal.
    If the number of pages cannot be predicted — use the nextPageToken property.
    nextPageToken
    String
    optional Token generated by the server for loading the next page. This token will be sent in the pageToken of the next /select request. Used to load data by pages when their total number cannot be predicted. 
    To load the next pages, the component will continue sending the /select requests while the nextPageToken is present in the response.
    If the number of pages can be predicted — use the pageTotal and page properties.

    Examples

    1. Example with one value
      Request:
      {
          "index": "dataset-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": "dataset-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": "dataset-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": "dataset-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 loading data by pages using the pageTotal and page
      Initial request:
      {
          "index": "dataset-123",
          "type": "select",
          "query": {
              "aggs": {
                  "values": [{
                      "func": "sum", 
                      "field": {
                         "uniqueName": "price"
                      }
                  }],
                  "by": {
                      "rows": [{
                          "uniqueName": "country"
                      }]
                  }
              }
          },
          "page": 0
      }
      First response:
      {
      "aggs": [{
      "values": {
      "price": {
      "sum": 12000
      }
      },
      "keys": {
      "country": "United States"
      }
      }, {
      "values": {
      "price": {
      "sum": 6000
      }
      },
      "keys": {
      "country": "Ukraine"
      }
      }],
      "pageTotal": 2,
      "page": 0
      }
      Next request:
      {
          "index": "dataset-123",
          "type": "select",
          "query": {
              "aggs": {
                  "values": [{
                      "func": "sum", 
                      "field": {
                         "uniqueName": "price"
                      }
                  }],
                  "by": {
                      "rows": [{
                          "uniqueName": "country"
                      }]
                  }
              }
          },
          "page": 1
      }
      Final response:
      {
      "aggs": [{
      "values": {
      "price": {
      "sum": 7000
      }
      },
      "keys": {
      "country": "Israel"
      }
      }, {
      "values": {
      "price": {
      "sum": 25000
      }
      }
      }],
      "pageTotal": 2,
      "page": 1
      }
    6. Example with loading data by pages using the nextPageToken
      Initial request:
      {
          "index": "dataset-123",
          "type": "select",
          "query": {
              "aggs": {
                  "values": [{
                      "func": "sum", 
                      "field": {
                         "uniqueName": "price"
                      }
                  }],
                  "by": {
                      "rows": [{
                          "uniqueName": "country"
                      }]
                  }
              }
          },
          "page": 0
      }
      First response:
      {
      "aggs": [{
      "values": {
      "price": {
      "sum": 12000
      }
      },
      "keys": {
      "country": "United States"
      }
      }, {
      "values": {
      "price": {
      "sum": 6000
      }
      },
      "keys": {
      "country": "Ukraine"
      }
      }],
      "nextPageToken": "secondPart"
      }
      Next request:
      {
          "index": "dataset-123",
          "type": "select",
          "query": {
              "aggs": {
                  "values": [{
                      "func": "sum", 
                      "field": {
                         "uniqueName": "price"
                      }
                  }],
                  "by": {
                      "rows": [{
                          "uniqueName": "country"
                      }]
                  }
              }
          },
          "pageToken": "secondPart"
      }
      Final response:
      {
      "aggs": [{
      "values": {
      "price": {
      "sum": 7000
      }
      },
      "keys": {
      "country": "Israel"
      }
      }, {
      "values": {
      "price": {
      "sum": 25000
      }
      }
      }]
      }
    7. Example with the selection filter
      Request:
      {
          "index": "dataset-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.
    8. Example with the selection filter on several fields
      Request:
      {
          "index": "dataset-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.
    9. Example with the conditional filter for a string field
      Request:
      {
          "index": "dataset-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.
    10. Example with the conditional filter for a number field
      Request:
      {
          "index": "dataset-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.
    11. Example with the conditional filter for a date field
      Request:
      {
      "index": "dataset-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.
    12.  Example with the value filter
      Request:
      {
          "index": "dataset-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