Query Pivot Table Details

1. Overview

Use the pivot table details query API to query detail-level data for a specified metric.

2. API URL

POST {anymetrics_host:anymetrics_port}/anymetrics/api/v1/metrics/queryMetricResultDetails

To obtain semantic_host:semantic_port, see Calling APIs.

3. Request Parameters

3.1 Common Request Headers

Parameter	Required	Description	Type
tenant-id	Yes	Tenant ID that identifies the tenant containing the query assets.	String
auth-type	Yes	Authentication type. Supported values: UID, TOKEN, ACCOUNT, APIKEY.	String
auth-value	Yes	Authentication value corresponding to auth-type.	String
queryBy	No	Identifier type used for the query. UUID: use the system internal UUID. If omitted, enName is used by default.	String

How to Obtain Common Parameters

In Aloudata CAN, select the metric application from the top navigation bar, then choose API Integration from the left navigation menu. You can obtain tenant-id on the API Integration page. Set auth-value to the authentication value required by the selected auth-type.

⚠️ Note

• When using UUIDs for metrics or dimensions, you must pass queryby = UUID.

• When querying by enName, you do not need to pass queryby.

4. Request Body

4.1 Parameter Overview

Parameter	Type	Required	Description
metric	string	No	Metric identifier. Use either metric or metricDefinition.
metricDefinition	object	No	Dynamic metric definition. Use either metricDefinition or metric.
columns	List	Yes	Detail columns to return.
filters	List	No	Filter conditions.
timeConstraint	String	No	Time constraint.
order	List>	No	Sort rules.
limit	int	No	Number of records to return.
offset	int	No	Offset.

4.2 metric / metricDefinition

Option 1: Query with metric (Recommended)

{
  "metric": "MEYZ0004"
}

Or in UUID mode:

{
  "metric": "mc1532d42ae99344a0d3e8534ccb4329"
}

Option 2: Use metricDefinition (Dynamic Period)

{
  "metricDefinition": {
    "refMetric": "MEYZ0004",
    "period": "to_date 0 day of 0 day"
  }
}

Field	Description
refMetric	Metric identifier (UUID or enName).
period	Time period definition. The syntax is the same as the semantic query API.

5. columns Query Column Description

5.1 Basic Format

['DATASET__<dataset_name>'/'<column_name>']

Example:

['DATASET__multi_test_orders'/'order_amount']

5.2 Specify a Return Alias

['DATASET__multi_test_users'/'username__alias__username']

Rules

Rule	Description
DATASET__	Fixed prefix that indicates a dataset.
__alias__	Specifies the returned field alias.
No alias specified	The system generates an alias automatically.

Example of the automatic alias rule:

['DATASET__users'/'username']
→ DATASET__users__username

---

6. filters

6.1 enName Mode (Default)

[
  "['username'] = \"wangxiaoming\"",
  "['district'] = \"Xihu District\""
]

---

6.2 UUID Mode (Requires queryby=UUID)

[
  "['dmf4c434088a43c8386dca248eccacd8'] = \"zhengxuemei\"",
  "['dm09dd147308de3d4346794a9572c016'] = \"Siming District\""
]

---

7. timeConstraint

"[metric_time__day] = \"2025-07-31\""

Notes:

• The time field must match the metric time granularity.

• Common fields:

• metric_time

• metric_time__day

---

8. order

[
  {
    "['DATASET__multi_test_users'/'username__alias__username']": "desc"
  }
]

Item	Description
key	Sort field. Use the same syntax as columns.
value	asc / desc

9. Complete Request Examples

9.1 enName Mode Example

{
    "metric": "MEYZ0004",
    "metricDefinition": null,
    "columns": [
        "['DATASET__multi_test_orders'/'order_amount']",
        "['DATASET__multi_test_orders'/'seller_id__alias__seller_id']",
        "['DATASET__multi_test_users'/'user_id__alias__user_id']",
        "['DATASET__multi_test_users'/'username__alias__username']"
    ],

    "filters": [
        "['username'] = \"wangxiaoming\"",
        "['district'] = \"Xihu District\""
    ],
    "timeConstraint": "[metric_time] = \"2025-04-01\"",
    "order": [
        {
            "['DATASET__multi_test_users'/'username__alias__username']": "desc"
        }
    ],
    "limit": 10,
    "offset": 0
}

---

9.2 UUID Mode Example (queryby=UUID)

{
    "metric": "mc1532d42ae99344a0d3e8534ccb4329",
    "metricDefinition": null,
    "columns": [
        "['DATASET__multi_test_orders'/'order_amount']",
        "['DATASET__multi_test_orders'/'seller_id__alias__seller_id']",
        "['DATASET__multi_test_users'/'user_id__alias__user_id']",
        "['DATASET__multi_test_users'/'username__alias__username']"
    ],

    "filters": [
        "['dmf4c434088a43c8386dca248eccacd8'] <> \"wangxiaoming\"",
        "['dm09dd147308de3d4346794a9572c016'] = \"Xihu District\""
    ],
    "timeConstraint": "[metric_time__day] = \"2025-04-01\"",
    "order": [
        {
            "['DATASET__multi_test_users'/'username__alias__username']": "desc"
        }
    ],
    "limit": 10,
    "offset": 0
}

---

9.3 metricDefinition Example

{
  "metricDefinition": {
    "refMetric": "MEYZ0004",
    "period": "to_date 0 day of 0 day"
  },
  "columns": [
    "['DATASET__multi_test_users'/'username__alias__username']"
  ],
  "timeConstraint": "[metric_time__day] = \"2025-07-31\""
}

---

10. Response Example

{
    "data": {
        "queryId": "095b6d5dea7c439a86c18bc0bb442067",
        "warning": null,
        "sql": "/* Trace ID: df6f8df5-d413-4647-9e0a-e74b40bbe4a2 */ SELECT `t4`.`username`, `t4`.`user_id`, `t4`.`seller_id`, `t4`.`order_amount` AS `DATASET__multi_test_orders__order_amount` FROM (SELECT `t3`.`seller_id`, `t3`.`order_amount`, `t3`.`day_col`, `t3`.`buyer_id`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`last_login_time`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`user_level`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`created_at`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`address_code`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`register_time`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`user_id`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`is_seller`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`updated_at`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`phone`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`check_time`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`email`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`id`, `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`username` FROM (SELECT `t0`.`seller_id`, `t0`.`order_amount`, `t2`.`day_col`, `t0`.`buyer_id` FROM (SELECT `tn_68155_aloudata_qq_datasource__default__multi_test_orders`.`seller_id`, `tn_68155_aloudata_qq_datasource__default__multi_test_orders`.`order_amount`, `tn_68155_aloudata_qq_datasource__default__multi_test_orders`.`buyer_id`, DATE_TRUNC('DAY', `tn_68155_aloudata_qq_datasource__default__multi_test_orders`.`order_date`) AS `f3` FROM `default_catalog`.`aloudatacan`.`tn_68155_aloudata_qq_datasource__default__multi_test_orders` AS `tn_68155_aloudata_qq_datasource__default__multi_test_orders` WHERE DATE_TRUNC('DAY', `tn_68155_aloudata_qq_datasource__default__multi_test_orders`.`order_date`) >= '2025-04-01' AND DATE_TRUNC('DAY', `tn_68155_aloudata_qq_datasource__default__multi_test_orders`.`order_date`) <= '2025-04-01') AS `t0` LEFT JOIN (SELECT `smart_date_table__20230101a847000000`.`day_col` FROM `aloudatacan`.`smart_date_table__20230101a847000000` AS `smart_date_table__20230101a847000000` WHERE `smart_date_table__20230101a847000000`.`day_col` >= CAST('2025-04-01' AS DATE) AND `smart_date_table__20230101a847000000`.`day_col` <= CAST('2025-04-01' AS DATE)) AS `t2` ON `t0`.`f3` = `t2`.`day_col`) AS `t3` LEFT JOIN `default_catalog`.`aloudatacan`.`tn_68155_aloudata_qq_datasource__default__multi_test_users` AS `tn_68155_aloudata_qq_datasource__default__multi_test_users` ON `t3`.`buyer_id` = `tn_68155_aloudata_qq_datasource__default__multi_test_users`.`user_id`) AS `t4` LEFT JOIN `default_catalog`.`aloudatacan`.`tn_68155_aloudata_qq_datasource__default__multi_test_region` AS `tn_68155_aloudata_qq_datasource__default__multi_test_region` ON `t4`.`address_code` = `tn_68155_aloudata_qq_datasource__default__multi_test_region`.`address_code` WHERE `t4`.`day_col` >= CAST('2025-04-01' AS DATE) AND `t4`.`day_col` <= CAST('2025-04-01' AS DATE) AND `tn_68155_aloudata_qq_datasource__default__multi_test_region`.`district` = CAST('Xihu District' AS VARCHAR) AND `t4`.`username` = CAST('wangxiaoming' AS VARCHAR) AND `t4`.`day_col` = CAST('2025-04-01' AS DATE) ORDER BY (`t4`.`username`) DESC LIMIT 10",
        "table": {
            "columns": {
                "user_id": [
                    {
                        "value": 1,
                        "flag": null,
                        "count": 1
                    }
                ],
                "seller_id": [
                    {
                        "value": 15,
                        "flag": null,
                        "count": 1
                    }
                ],
                "DATASET__multi_test_orders__order_amount": [
                    {
                        "value": 8999.00,
                        "flag": null,
                        "count": 1
                    }
                ],
                "username": [
                    {
                        "value": "wangxiaoming",
                        "flag": null,
                        "count": 1
                    }
                ]
            },
            "rows": null
        },
        "metas": [
            {
                "id": null,
                "uuid": null,
                "name": "username",
                "dataType": null,
                "dataTypeName": "VARCHAR",
                "displaySize": null,
                "displayName": null,
                "schemaName": null,
                "scale": null,
                "precision": null,
                "tableName": null,
                "type": null
            },
            {
                "id": null,
                "uuid": null,
                "name": "user_id",
                "dataType": null,
                "dataTypeName": "INT",
                "displaySize": null,
                "displayName": null,
                "schemaName": null,
                "scale": null,
                "precision": null,
                "tableName": null,
                "type": null
            },
            {
                "id": null,
                "uuid": null,
                "name": "seller_id",
                "dataType": null,
                "dataTypeName": "INT",
                "displaySize": null,
                "displayName": null,
                "schemaName": null,
                "scale": null,
                "precision": null,
                "tableName": null,
                "type": null
            },
            {
                "id": null,
                "uuid": null,
                "name": "DATASET__multi_test_orders__order_amount",
                "dataType": null,
                "dataTypeName": "DECIMAL",
                "displaySize": null,
                "displayName": null,
                "schemaName": null,
                "scale": null,
                "precision": null,
                "tableName": null,
                "type": null
            }
        ],
        "hitMvType": "NOT_REWRITE",
        "containsErrorColumn": false
    },
    "success": true,
    "code": "200",
    "errorMsg": null,
    "detailErrorMsg": null,
    "traceId": "df6f8df5-d413-4647-9e0a-e74b40bbe4a2"
}