Need a special offer?Find out if your project fits.
+
API reference
  • API Reference for older versions
  • Table of contents

    /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
    }
    Parameter/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., the filters.advanced property is set to false), the filter's structure is an array of FilterObjects.
    If multilevel hierarchies are supported, the filter can be:
    page
    Number
    The page number. It can be used to load data by parts. If the response contains the pageTotal parameter, additional requests will be performed to load the remaining pages. Starts from 0.

    Response

    {
      "aggs": AggregatedDataObject[],
      "page": number,
      "pageTotal": number
    } 
    Parameter/Type Description
    aggs
    AggregatedDataObject[]
    Aggregated data.
    page
    Number
    optional The current page number. Should start from 0.
    pageTotal
    Number
    optional 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