Public API v2 → Query Endpoint

About the /query endpoint

The /query endpoint queries a single business view into a listing table, aggregated table, or Pivot table, enabling other systems to query and consume Incorta business views.

Any tenant user with a valid access token can use this endpoint to query a business view.

For information about how to get started with Public API v2, refer to References → Public API v2.

Note

You must use the HTTPS scheme to be able to successfully run the API endpoints. Additionally, you must specify the tenant name (case-sensitive) in the endpoint request URL starting with the 2023.7.0 release.

Important

This endpoint simulates creating a table insight referencing a business schema view. The response of this endpoint is either a CSV or JSON file.

Request URL

 https://<cluster_URL>/incorta/api/v2/{tenantName}/query

Request URL Parameter

Field name	Description	Type	Required
tenantName	The tenant name (case-sensitive)	String	Required starting with 2023.7.0

HTTP method

POST

Request headers

{
"Content-Type": "application/json",
"Accept": "application/json",
"Authorization": "Bearer {access_token}"
}

You must use a valid access token of a tenant user.

For information about how to create an access token, refer to Public API v2 → Create an API access token.

Request body

{
  "stringify": boolean,
  "username": "string",
  "query": {
    "aggregate": boolean,
    "ensureParentSubtotal": boolean,
    "skipSelf": boolean,
    "sortWithinGroups": boolean,
    "sampled": boolean,
    "formatted": boolean,
    "startRow": number,
    "pageSize": number,
    "format": "string",
    "auditingInfo": {
      "dashboardGuid": "string",
      "insightGuid": "string",
      "dashboardLayoutGuid": "string",
      "dashboardName": "string",
      "insightName": "string",
      "dashboardLayoutName": "string"
    },
    "filters": [
      {"type": "string",
        "label": "string",
        "fieldKey": "string",
        "formulaKey": "string",
        "value": {
          "values": [
            "string"],
          "op": "string",
          "options": {
            "caseSensitive": boolean,
            "level": number,
            "depth": number}},
        "prompt": boolean}
    ],
    "aggregateFilters": [
      {"field": "string",
        "formula": "string",
        "op": "string",
        "values": [
          "string"],
        "aggregation": "string"}
    ],
    "distinctFilter": {
      "fields": [
        {"label": "string",
          "name": "string"}]
    },
    "sorting": [
      {"field": "string",
        "label": "string",
        "formula": "string",
        "dir": "string"}
    ],
    "measures": [
      {"field": "string",
        "formula": "string",
        "label": "string",
        "formats": [
          {"format": "string",
            "type": "string"}],
        "fallbackFormat":
          {"format": "string",
            "type": "string"},
        "sourceField": "string",
        "aggregation": "string",
        "scale": "string",
        "filters": [
          {"type": "string",
            "label": "string",
            "fieldKey": "string",
            "formulaKey": "string",
            "value": {
              "values": [
                "string"],
              "op": "string",
              "options": {
                "caseSensitive": boolean,
                "level": number,
                "depth": number}},
            "prompt": boolean}]}
    ],
    "rows": [
      {"field": "string",
        "formula": "string",
        "label": "string",
        "formats": [
          {"format": "string",
            "type": "string"}],
       "fallbackFormat":
          {"format": "string",
            "type": "string"},
        "sorting": [
          {"field": "string",
            "label": "string",
            "formula": "string",
            "dir": "string"}],
        "showEmptyGroups": boolean,
        "datePart": boolean,
        "subTotal": boolean,
        "hierarchy": {
          "level": number,
          "excludeParent": boolean,
          "attributes": [
            {"label": "string",
              "name": "string",
              "fallbackFormat":
                {"format": "string",
                  "type": "string"},
              "formats": [
                {"format": "string",
                  "type": "string"}]}]}}
    ],
    "columns": [
      {"field": "string",
        "formula": "string",
        "label": "string",
        "formats": [
          {"format": "string",
            "type": "string"}],
        "fallbackFormat":
          {"format": "string",
            "type": "string"},
        "sorting": [
          {"field": "string",
            "label": "string",
            "formula": "string",
            "dir": "string"}],
        "datePart": boolean}
    ]}}

Note

In 2023.4.0, you must add a value or an empty string to the label parameter when defining a formula as a measure, row, column, measure filter, or individual filter. However, starting with 2023.4.1, you don’t need to add a label as the endpoint will use "New Formula" as a default header.
The the formatted and formats properties are available starting with 2023.4.1 to specify format options for dates and numbers in measures, rows, columns, and hierarchy attributes.
An additional option for the format property is available starting with 2024.1.0. You can set the format of date and number fields to "inherit" the default format specified at the business view column level. You can also use the new property fallbackFormat to specify the format to use if no default format is set at the view column level or if the inherited format is not applicable to the query field.

Request body parameters

The following table describes the high-level parameters of the request body.

Parameter	Type	Description	Required
query	Object	The parameters of the query object. For details, see Query object.	Required
stringify	Boolean	● Set to `true`, which is the default value, to return the response as a string following the JSON notation. ● Set to `false` to return the response as a JSON object or CSV according to the `format` specified in the Query object.	Optional
username	String	Specify the login name of the user account that you want to use (impersonate) to run the query. Use the following format: `"base64.encode(loginName)"`	Optional

Query object

The following table describes the sub-objects and arrays in the query object in the request body.

Parameter	Type	Description	Required
aggregate	Boolean	● Set to `true` to create a query for an aggregated table or a Pivot table. ● Set to `false` to create a query for a listing table. The default is `false`.	Optional
ensureParentSubtotal	Boolean	Set to `true` to create an aggregated table or a hierarchy table. This simulates the Merge Columns option in the insight settings.	Optional
skipSelf	Boolean	In hierarchy queries, set to `true` to skip counting the self-node to be accounted for in pagination.	Optional
sortWithinGroups	Boolean	Specify if you want to sort the subcategories within groups. Applicable to aggregate tables only.	Optional
sampled	Boolean	Specify if you want to use sampled data to create the response file or to use the whole data set	Optional
formatted	Boolean	Specify if you want to apply the format options specified for measures, rows, columns, and hierarchy attributes. If you set this parameter to `false`, the endpoint will not apply any formats.	Optional
filters	Array	Define criteria to filter out rows returned by the query. You can use a business schema view column as a filter column and define the filter operator and filter values or specify a filter expression in a formula. For details, see Query filters.	Optional
aggregate filters	Array	Add aggregate filters using columns or formulas. Applicable to aggregated tables only. You must define the aggregation function for each filter column. In the case of formulas, the formula expression must have an aggregation function as well. For details, see Query aggregate filters.	Optional
distinct filters	Object	Add criteria to show distinct values. Applicable to listing and aggregated table queries only. You can reference only business schema view columns. For details, see Query distinct filters.	Optional
sorting	Array	Define sorting options for the query result. For details, see Query sorting.	Optional
startRow	Number	Specify the index of the first row you want to return in the response file.	Optional
pageSize	Number	Specify the total number of rows you want to return in the response file	Optional
format	String	The format of the response file. The valid values are: ● "json" (default) ● "csv"	Optional
auditingInfo	Object	Audit information for tracking the query transactions. For details, see Query auditing info.	Optional
measures	Array of objects	The list of columns and formulas to include as the query measures. You must add at least one measure. The maximum is 1000 measures. For details, see Query measures.	Required
rows	Array of objects	Rows in the Pivot table or the grouping dimensions in listing and aggregated tables. Accepts business schema view columns and formulas along with their properties. For details, see Query rows: Grouping Dimensions.	● Required in Pivot and Aggregated tables. ● Optional in listing tables.
columns	Array of objects	Columns in the Pivot table. You can reference business schema columns or formulas. Applicable to Pivot tables only. For details, see Query columns: Pivot table columns.	● Required in Pivot tables only. ● Not valid for listing or aggregated tables.

Query measures

Use this array to add the formulas and columns you want to reference in the query and define the properties of each measure. You must define the query measures for all tables.

Parameter	Type	Description	Required
field	String	The fully qualified name of the business schema view column `businessSchemaName.viewName.columnName`	Required if no formula is defined
formula	String	The formula expression	Required if no field is defined
label	String	The label of the column or formula that appears as the column header in the output file. If you don’t set a label, the response shows the column name or "New Formula" respectively.	Optional
formats	Array of objects	Specify the formats you want to apply to dates and numbers.	Optional
formats > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`. ● Starting with 2024.1.0, for both dates and numbers, you can set the format to `inherit` to use the default format specified at the business schema view column if exists. If no format is set at the view column level, the fallback format is used.	Optional
formats > type	String	Specify the format type. The valid values are: ● "num" ● "date".	Optional
fallbackFormat	Object	Specify the formats you want to apply to dates and numbers when the inherited business view column format is undefined (that is, not set) or cannot be applied to the query field, for example, when the format is not applicable to the aggregated data type.	Optional
fallbackFormat > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`.	Optional
fallbackFormat > type	String	Specify the format type. The valid values are: ● "num" ● "date".	Optional
sourceField	String	The fully qualified name of the source physical schema column that represents the level column. Applicable only when defining a hierarchy table.	Required when creating a hierarchy table
aggregation	String	The aggregation function to apply to the column or formula. Applicable to Pivot and aggregated tables only. The valid values are: ● "sum" ● "count" ● "distinct" ● "median" ● "average" ● "min" ● "max"	Required in the case of Pivot and aggregated tables only.
scale	String	The scale of the aggregated values. Applicable to Pivot and aggregated tables only. The valid values are: ● "k" ● "m" ● "percent"	Optional
filters	Array of objects	Filters to apply to each aggregated measure. Applicable to Pivot and aggregated tables only.	Optional

Measure filters

This array is optional and is applicable to Pivot and aggregated tables only. You can add multiple filters using either business schema view columns or formulas. The following table shows the parameters for each filter.

Parameter	Type	Description	Required
type	String	The filter type: column or formula The valid values are: ● "fieldKey" ● "formulaKey"	Required
label	String	The label of the column or formula that appears as the column header in the output file. If you don’t set a label, the response shows the column name or "New Formula" respectively.	Optional
fieldKey	String	The fully qualified name of the column if the type is set to fieldKey.	Required if the type is set to fieldKey
formulaKey	String	The formula if the type is set to formualKey. Example: `"Online_Store.Customers.Total_Sales * 0.25"`	Required if the type is set to formulaKey
value	Object with arrays	The filter values, operator, and options	Optional
value > values	Array of string values	The filter values	Optional
value > op	String	The filter operator. The valid values are: ● "=" ● "!=" ● ">" ● ">=" ● "<" ● "<=" ● "BETWEEN" ● "IN_LIST" ● "NOT_IN_LIST" ● "NULL" ● "IS_NOT_NULL" ● "STARTS_WITH" ● "DOSES_NOT_STARTS_WITH" ● "CONTAINS" ● "DOSES_NOT_CONTAIN" ● "ENDS_WITH" ● "FIRST_VERSION" ● "LAST_VERSION" ● "IS_DESCENDANT" ● "IN_QUERY" ● "TRUE" ● "FALSE"	Optional
value > options	Object with multiple elements	The options of the filter values	Optional
options > caseSensitive	Boolean	Specify if the string provided as a value is case-sensitive or not	Optional
options > level	Number	Specify the level of data in the case of the "IS_DESCENDANT" operator	Optional
options > depth	Number	Specify the depth of data in the case of the "IS_DESCENDANT" operator	Optional
prompt	Boolean	Specify if this filter is used in prompts or not.	Optional

Query rows: Grouping Dimension

Use this array to define rows in the Pivot table or the grouping dimensions in listing and aggregated tables. You can use business schema view columns and formulas. You must define the query rows in the case of a Pivot or aggregated table only; however, it is optional in the case of a listing table.

Parameter	Type	Description	Required
field	String	The fully qualified name of the business schema view column	Required if no formula is defined
formula	String	The formula expression	Required if no field is defined
label	String	The label of the column or formula that appears as the column header in the output file. If you don’t set a label, the response shows the column name or "New Formula" respectively.	Optional
formats	Array of objects	Specify the formats you want to apply to dates and numbers.	Optional
formats > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`. ● Starting with 2024.1.0, for both dates and numbers, you can set the format to `inherit` to use the default format specified at the business schema view column if exists. If no format is set at the view column level, the fallback format is used.	Optional
formats > type	String	Specify the format type. The valid values are: ● "num" ● "date".	Optional
fallbackFormat	Object	Specify the formats you want to apply to dates and numbers when the inherited business view column format is undefined or cannot be applied to the query field, for example, when the format is not applicable to the aggregated data type.	Optional
fallbackFormat > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`.	Optional
fallbackFormat > type	String	Specify the format type. The valid values are: ● "num" ● "date".	Optional
sorting	Array	Define sorting options for each column	Optional
sorting > field	String	The business schema view column to sort the values by	Required if no filter formula is defined
sorting > formula	String	The formula to sort the values by	Required if no filter field is defined
sorting > label	String	The label of the sorting field or formula	Optional
sorting > dir	String	The direction of the sorting order: ascending or descending. The valid values are: ● "asc" ● "desc"	Optional
showEmptyGroups	Boolean	Specify if you want to return rows for which the grouping dimension contains a null value	Optional
datePart	Boolean	Specify if the field is part of time series (Year, Quarter, Month, and Day).	Optional
subTotal	Boolean	Specify if you want to return the subtotals or not.	Optional
hierarchy	Object with elements	Applicable only to columns from objects with self-join	Optional
hierarchy > level	Number	Specify the level of data in the hierarchy that you want to include in the query for the respective column.	Optional
hierarchy > excludeParent	Boolean	Specify if you want to exclude the parent row or not	Optional
hierarchy > attributes	Array of objects	Specify the dimension attributes to be added next to each dimension value. This applies only to hierarchical queries with a single dimension.	Optional
attributes > label	String	Specify the attribute label	Optional
attributes > name	String	Specify the attribute column, for example, `"SALES.PRODUCT.PROD_NAME"`	Optional
attributes > fallbackFormat	Object	Specify the format you want to apply to dates or numbers when the inherited business view column format is undefined or cannot be applied to the attribute column.	Optional
fallbackFormat > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`.	Optional
fallbackFormat > type	String	Specify the format type	Optional
attributes > formats	Array of objects	Specify the formats you want to apply to dates and numbers.	Optional
formats > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`. ● Starting with 2024.1.0, for both dates and numbers, you can set the format to `inherit` to use the default format specified at the business schema view column if exists. If no format is set at the view column level, the fallback format is used.	Optional
formats > type	String	Specify the format type. The valid values are: ● "num" ● "date".	Optional

Query columns: Pivot table columns

Use this array to define columns in the Pivot table. You can add business schema columns and formulas.

Parameter	Type	Description	Required
field	String	The fully qualified name of the business schema view column	Required if no formula is defined
formula	String	The formula expression	Required if no field is defined
label	String	The label of the column or formula that appears as the column header in the output file. If you don’t set a label, the response shows the column name or "New Formula" respectively.	Optional
formats	Array of objects	Specify the formats you want to apply to dates and numbers.	Optional
formats > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`. ● Starting with 2024.1.0, for both dates and numbers, you can set the format to `inherit` to use the default format specified at the business schema view column if exists. If no format is set at the view column level, the fallback format is used.	Optional
formats > type	String	Specify the format type. The valid values are: ● "num" ● "date".	Optional
fallbackFormat	Object	Specify the formats you want to apply to dates and numbers when the inherited business view column format is undefined or cannot be applied to the query field, for example, when the format is not applicable to the aggregated data type.	Optional
fallbackFormat > format	String	Specify the format you want. ● For numbers, you can use formats such as `"###,##0.00"` ● For dates, you can use formats such as `"SHORT"`,`"LONG"` and `"M/d/yy"`.	Optional
fallbackFormat > type	String	Specify the format type. The valid values are: ● "num" ● "date".	Optional
sorting	Array	Define sorting options for each column	Optional
sorting > field	String	The business schema column to sort the values of the Pivot table column by	Required if no sorting formula is defined
sorting > formula	String	The formula to sort the values of the Pivot table column by	Required if no sorting field is defined
sorting > label	String	The label of the sorting field or formula	Optional
sorting > dir	String	The direction of the sorting order: ascending or descending. The valid values are: ● "asc" ● "desc"	Optional
datePart	Boolean	Specify if the field is part of time series (Year, Quarter, Month, and Day).	Optional

Query filters: Individual filter

Use this array to define criteria to filter out rows returned by the query. You can use a business schema view column as a filter column and define the filter operator and filter values or specify a filter expression in a formula. This part of the query is optional.

Parameter	Type	Description	Required
type	String	Specify the type of the individual filter: column or formula The valid values are: ● "fieldKey" ● "formulaKey"	Required
label	String	The label of the column or formula that appears as the column header in the output file. If you don’t set a label, the response shows the column name or "New Formula" respectively.	Optional
fieldKey	String	The fully qualified name of the business schema view column	Required if the type is set to fieldKey
formulaKey	String	The formula expression	Required if the type is set to formulaKey
value	Object	with an array of values The filter values, operator, and options	Required
values	Array of string values	The filter values	Required
value > op	String	The filter operator The valid values are: ● "=" ● "!=" ● ">" ● ">=" ● "<" ● "<=" ● "BETWEEN" ● "IN_LIST" ● "NOT_IN_LIST" ● "NULL" ● "IS_NOT_NULL" ● "STARTS_WITH" ● "DOSES_NOT_STARTS_WITH" ● "CONTAINS" ● "DOSES_NOT_CONTAIN" ● "ENDS_WITH" ● "FIRST_VERSION" ● "LAST_VERSION" ● "IS_DESCENDANT" ● "IN_QUERY" ● "TRUE" ● "FALSE"	Optional
value > options	Object	The filter value options	Optional
options > caseSensitive	Boolean	Specify if the string provided as a value is case-sensitive or not	Optional
options > level	Number	Specify the level of data in the case of the "IS_DESCENDANT" operator	Optional
options > depth	Number	Specify the depth of data in the case of the "IS_DESCENDANT" operator	Optional
prompt	Boolean	Specify if this filter is used in prompts or not	Optional

Query aggregate filters

You can add aggregate filters to aggregated and Pivot tables only. You can create multiple aggregate filters using columns or formulas. You must define the aggregation function for each filter column you add. In the case of formulas, the formula expression must have an aggregation function as well. This part is optional.

Parameter	Type	Description	Required
field	String	The fully qualified name of the business schema view column you want to use in the aggregate filter	Required if no formula is defined
formula	String	The formula you want to use as the aggregate filter. The formula must contain the whole filter expression, including a filter operator and filter values. Example: `"SUM(SALES.PRODUCTS.PROD_COST) > 100000"`	Required if no field is defined
op	String	The filter operator in the case of using a filter column. The valid values are: ● "=" ● "!=" ● ">" ● ">=" ● "<" ● "<=" ● "BETWEEN" ● "IN_LIST" ● "NOT_IN_LIST" ● "NULL" ● "IS_NOT_NULL" ● "STARTS_WITH" ● "DOSES_NOT_STARTS_WITH" ● "CONTAINS" ● "DOSES_NOT_CONTAIN" ● "ENDS_WITH" ● "FIRST_VERSION" ● "LAST_VERSION" ● "IS_DESCENDANT" ● "IN_QUERY" ● "TRUE" ● "FALSE"	Optional
values	Array of strings	The filter values	Optional
aggregation	String	The aggregation function to apply to the filter column. The valid values are: ● "sum" ● "count" ● "distinct" ● "median" ● "average" ● "min" ● "max"	Optional

Query distinct filters

You can specify distinct filters in listing and aggregated table queries only. You can reference only business schema view columns. For each filter, you must define the column to use. You can also define a label for this filter. This part is optional.

Parameter	Type	Description	Required
label	String	The label of the distinct filter column. If you don’t specify a label, the column name is used.	Optional
name	String	The fully qualified name of the business schema view column you want to filter the result according to its distinct values	Required

Query sorting

Use this array to define sorting options for the query result. This part is optional.

Parameter	Type	Description	Required
field	String	The business schema view column to sort the query result by	Optional
label	String	The label of the sorting field or formula	Optional
formula	String	The formula to sort the query result by	Optional
dir	String	The direction of the sorting order: ascending or descending. The valid values are: ● "asc" ● "desc"	Optional

Query auditing info

Use this object to specify the audit information for tracking the query transactions.

Parameter	Type	Description	Required
dashboardGuid	String	The GUID of the dashboard	Optional
insightGuid	String	The GUID of the insight	Optional
dashboardLayoutGuid	String	The GUID of the dashboard layout	Optional
dashboardName	String	The dashboard name	Optional
insightName	String	The insight name	Optional
dashboardLayoutName	String	The dashboard layout name	Optional

Request body examples

The following are examples that reference the Online_Store business schema.

Listing table example

{
       "stringify": "false",
       "query": {
       "aggregate": "false",
       "format": "json",
       "measures": [
           {"field": "Online_Store.Customers.AccountNumber",
               "label": "Account Number"},
           {"field": "Online_Store.Customers.CustomerID",
               "label": "Customer ID"},
            {"field": "Online_Store.Customers.Sales_Order_Count",
               "label": "Sales Order Count"},
           {"field": "Online_Store.Customers.Average_Sales_Order",
               "label": "Average Sales Order"},
             {"field": "Online_Store.Customers.Total_Sales",
               "label": "Total Sales"},
           {"field": "Online_Store.Customers.Rank",
               "label": "Rank"},
             {"field": "Online_Store.Customers.StateProvinceName",
               "label": "State"},
           {"field": "Online_Store.Customers.City",
               "label": "City"}
       ]}}

Aggregated table example

{
       "stringify": "false",
       "query": {
           "aggregate": "true",
           "format": "json",
           "rows": [
               {"field": "Online_Store.OrderDetails.OrderDateYear",
                   "label": "Year (Order Date)"},
               {"field": "Online_Store.OrderDetails.OrderDateMonth",
                   "label": "Month (Order Date)",
                   "sorting" : [
                     {"field": "Online_Store.OrderDetails.OrderDateMonthNumber",
                           "label": "Month Order",
                           "dir": "asc"}]},
               {"field": "Online_Store.OrderDetails.SubcategoryName",
                   "label": "Subcategory"},
               {"field": "Online_Store.OrderDetails.ProductName",
                   "label": "Product"}
],
           "measures": [
               {"field": "Online_Store.OrderDetails.OrderQty",
                   "label": "Orderqty",
                   "aggregation" : "sum"},
               {"field": "Online_Store.OrderDetails.UnitPrice",
                   "label": "Unitprice",
                   "aggregation" : "sum"},
               {"field": "Online_Store.OrderDetails.UnitPriceDiscount",
                   "label": "Discount",
                   "aggregation" : "sum"}
],
           "filters": [
{"type": "fieldKey",
               "label": "Year(Order Date)",
               "fieldKey": "Online_Store.OrderDetails.OrderDateYear",
               "value": {
                   "values": ["2012-2014"],
                   "op": "BETWEEN"}}
]}}

Pivot table example

{
      "stringify": "false",
      "query": {
          "aggregate": "true",
          "format": "csv",
          "ensureParentSubtotal":"true",
          "rows": [
{"field": "Online_Store.Customers.StateProvinceName",
                  "label": "State",
                  "showEmptyGroups": "false"}
          ],
          "columns": [
          {"field": "Online_Store.Customers.City",
              "label": "City"}
     ],
          "measures": [
              {"field": "Online_Store.Customers.Sales_Order_Count",
                  "label": "Sales Order Count",
                  "aggregation" : "sum"},
              {"field": "Online_Store.Customers.Total_Sales",
                  "label": "Total Sales",
                  "aggregation" : "sum"}
          ],
          "filters": [
              {"type": "fieldKey",
                  "label": "Account Number",
                  "fieldKey": "Online_Store.Customers.AccountNumber",
                  "value": {
                      "values": ["AW00000001","AW00000002","AW00000003","AW00000004","AW00000005","AW00000006","AW00000007"],
                      "op": "IN_LIST"}
         }]}}

Hierarchy table example

{
       "stringify": "false",
       "query": {
           "aggregate": "true",
           "format": "json",
           "ensureParentSubtotal": "true",
           "sortWithinGroups" : "true",
           "rows": [
               {"field": "Online_Store.RevenueDetail.CategoryName",
                   "label": "Category",
                   "subTotal": "true"},
               {"field": "Online_Store.RevenueDetail.SubcategoryName",
                   "label": "Subcategory",
                   "subTotal": "true"}
           ],
           "measures": [
               {"field": "Online_Store.RevenueDetail.LineCost",
                   "label": "Cost",
                   "aggregation" : "sum"},
                {"field": "Online_Store.RevenueDetail.LineTotal",
                   "label": "Revenue",
                   "aggregation" : "sum"}
     ]}}

Endpoint response

The response of the endpoint can be in a CSV or JSON format according to the format parameter.

The following table shows the HTTP response status codes for this endpoint.

Code	Description	Payload Response
200	OK The endpoint returns the query result in the specified format successfully. The response of the endpoint can be in a CSV or JSON format according to the format parameter. For details, see Response examples.	`{"data": "string"}`
400	Bad Request The endpoint couldn’t run the query due to incorrect or missing request parameters.	`{"message": "string"}` Example: `{can't parse JSON. Raw result: INC_05000301: Runtime exception occurred [null].}`
401	Unauthorized The tenant name doesn’t match the tenant in the access token.	`{"message": "string"}`
403	Forbidden The endpoint couldn’t run the query due to authentication errors, such as an invalid or expired access token.	`{"message": "string"}` Example: `{"message": "INC_09030101: Invalid access token or access token may have expired."}`
404	Not Found The tenant doesn't exist or is disabled.	`{"message": "string"}`
422	Unprocessable Entity The endpoint couldn’t run the query due to referencing objects other than one business schema view.	`{"errorMessages": [{"message": "string"}]}` Example: `{"message": "INC_04050771: Physical schema OnlineStore is used, only business schemas are allowed in public API."}`
500	Internal Server Error The endpoint couldn’t run the query due to a server error.	`{"message": "string"}`

Response examples

The following are examples of the endpoint response when the endpoint returns the query result successfully (code 200).

Note

In 2023.4.0, the returned data will not be formatted when querying a business schema view or Incorta Analyzer view, and date values will be returned in the following format: yyyy-mm-dd 00:00:00.
When querying an Incorta SQL view while setting the query’s format option to json and regardless of the formatted parameter, the response will contain an additional attribute: formatted. This attribute shows the same data as the value attribute, except in the case of a date, it will shows the value in the following format: m/d/yy.

Example:

data": [
     {"value": "AW00000676",
       "formatted": "AW00000676"},
     {"value": "676",
       "formatted": "676"},
    {"value": "2020-01-30 00:00:00",
       "formatted": "1/30/20"}
]

Response example in CSV format:

"Account Number","Customer ID","Sales Order Count","Average Sales Order","Total Sales","Rank","State","City"
"AW00000170","170","1348","63726.64965107999","85903523.72965583","1","Tennessee","Memphis"
"AW00000166","166","1265","67820.8349202214","85793356.17408007","2","Vermont","Derby"
"AW00000024","24","1013","79331.27349487085","80362580.05030417","3","New York","Queens Village"
"AW00000254","254","1256","63738.705722391605","80055814.38732386","4","Washington","Bellevue"
"AW00000085","85","732","105860.65467478275","77489999.22194098","5","Washington","Easton"
"AW00000697","697","1017","75861.21537671397","77150856.03811811","6","Utah","Tooele"
...

Response example in JSON format:

{
"isSampled": false,
   "subqueryComplete": true,
   "rowHeaders": [
       {"label": "Category",
           "dataType": "string",
           "index": 0},
       {"label": "Subcategory",
           "dataType": "string",
           "index": 1}
   ],
   "measureHeaders": [
       {"label": "Cost",
           "dataType": "double"},
       {"label": "Revenue",
           "dataType": "double"}
   ],
   "data": [
       ["Accessories",
           "Bike Racks",
           "1.0547742480000407E7",
           "1.7510340792000014E7"],
       ["Accessories",
           "Bike Stands",
           "1048147.7160000057",
           "2802534.0"],
    [...],
       ["Components",
           null,
           "8.670673554101496E8",
           "9.067912530366442E8"]
   ],
   "isAggregated": true,
   "startRow": 0,
   "endRow": 39,
   "totalRows": 39,
   "complete": true,
   "raw": true
}

The JSON response contains additional information that the CSV file doesn’t show. The following table shows the additional parameters in the JSON response:

Parameter	Type	Description
isSample	Boolean	Specifies if data is sampled or not
rowHeaders	Array of strings	The grouping dimension headers
measureHeaders	Array of strings	The measure headers
colHeaders	Array of strings	Applicable on pivot table only column headers
pivotColumnHeaders	Array of strings	The headers of the Pivot table columns
Data	Array of arrays	The response data
isAggregated	Boolean	Specifies if the response is aggregated or not
startRow	Number	The request starting row
endRow	Number	The request end row
totalRows	Number	The count of the source rows
Complete	Boolean	Specifies if the request is complete or not. This is when the number of the total rows equals the number of the source rows.
Raw	Boolean	Specifies if data is formatted or not

Error message examples

The following are examples of the error messages that you may receive when you use this endpoint.

Error Message	Description
`INC_04050772: only one Business Schema and Business view is allowed, provided schemas [OnlineStore, Online_Store] and views [address, Customers]`	You referenced multiple views or business schemas in the request.
`INC_04050771: Physical schema OnlineStore is used, only business schemas are allowed in public API.`	You referenced a physical schema table instead of a single business schema view.
`INC_04050707: Undefined column Online_Store.Customers.Hello, either the column or the table it belongs to has been deleted.`	A column you referenced in the request couldn’t be found.
`"errorMessages" : [{"message" : "INC_01210000: Measure field or formula, one of them should have value"} ]`	You didn’t add a value for the field or formula properties of a measure object.
`"message" : "Encoded value is a not a Base64 string: username"`	You didn’t use the correct format to encode the username for impersonation.
`"message" : "INC_09040104: You cannot query data on behalf of another super user"`	You tried to impersonate another Super User, which you can’t.