# Statistics Query ## Basic statistic query format {% hint style="success" %} **query:** *query* includes following options items below for grouping.\ \ **Note**: Either of **distinct** option or **aggregate** option must be given. {% endhint %} | NO | KEY | TYPE | REQUIRED | DESC | | -- | ---------- | ---- | :------------------: | --------------------------------------------------------- | | 1 | distinct | list | :white\_check\_mark: | grouping in distinct condition with given items | | 2 | aggregate | list | :white\_check\_mark: | grouping and set fields for grouping | | 3 | filter | list | :x: | filter out within operator | | 4 | filter\_or | list | :x: | filter out within operator | | 5 | sort | dict | :x: | sorting a list with given keys | | 6 | page | dict | :x: | set limit and its start page in grouping query to display | {% hint style="info" %} **FILTER:** *filter* performs within **AND** condition \ :point\_right: \ and \ and \ ... {% endhint %} {% hint style="info" %} **FILTER\_OR:** *filter\_or* performs within **OR** condition \ :point\_right: \ or \ or \ ... {% endhint %} {% tabs %} {% tab title="SKELETON" %} ```javascript { "query": { "distinct": ..., "aggregate": {...}, "filter": [...], "filter_or": [...], "sort": {...}, "limit":{...} } } ``` {% endtab %} {% tab title="Distinct" %} ```javascript { "query": { "distinct": "server_id" } }{ ``` {% endtab %} {% tab title="Aggregate" %} ```javascript "query": { "aggregate": { "unwind": [ { "path": "collection_info.service_accounts" } ], "group": { "keys": [ { "key": "service_account_id", "name": "service_account" } ], "fields": [ { "operator": "count", "name": "cloud_service_count" } ] }, "count": { "name": "service_counts" } } } } ``` ### aggregate specification : (type: dict) | NO | KEY | TYPE | REQUIRED | DESC | | -- | ---------- | ---- | :--------------------------------: | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 1 | **unwind** | list | :white\_check\_mark: (conditional) |

set of list to separate items within given paths

for more information at $unwind (aggregation)

| | 2 | **group** | list | :white\_check\_mark: (conditional) | list of grouping keys and its alias | | 3 | **count** | dict | :white\_check\_mark: (conditional) | count of grouping results with given name | {% hint style="warning" %} Either of '**group**' or '**unwind**' or '**count**' must declare under aggregate parameters\ **Note**: Applying order on aggregate\ **unwind** > **group** > **count** {% endhint %} ### unwind specification : *(type: list)* | NO | KEY | TYPE | REQUIRED | DESC | | -- | -------- | ------ | -------------------- | ------------------------- | | 1 | **path** | string | :white\_check\_mark: | path to decomposable item | ```javascript { "unwind": [ { "path": "collection_info.service_accounts" } ] } ``` ### group specification : *(type: dict)* | NO | KEY | TYPE | REQUIRED | DESC | | -- | ---------- | ---- | -------------------- | ------------------------------------------- | | 1 | **keys** | list | :white\_check\_mark: | list of grouping keys and its alias | | 2 | **fields** | list | :x: | fields to express each alias with operators | ```javascript { "keys": [ { "key": "service_account_id", "name": "service_account" } ], "fields": [ { "operator": "count", "name": "cloud_service_count" } ] } ``` ```javascript { "keys": [ { "key": "created_at", "name": "created", "date_format": "%Y-%m-%d" } ], "fields": [ { "key" : "values.success_count" "name": "success_count" "operator": "max", }, { "key" : "values.fail_count" "name": "fail_count" "operator": "max", }, { "name": "year", "value": "now/d - 7d", "operator": "date", "date_format": "%Y" }, { "name": "month", "value": "now/d - 7d", "operator": "date", "date_format": "%m" }, { "name": "day", "value": "now/d - 7d", "operator": "date", "date_format": "%d" } ] } ``` #### keys specification : (type: list) Note: each items in keys applies to given name(alias) | NO | KEY | TYPE | REQUIRED | DESC | | -- | ---------------- | ------ | :------------------: | ------------------------------------------------------------------------------------------------------------------------------------- | | 1 | **key** | string | :white\_check\_mark: | key for grouping within declared resource type | | 2 | **name** | string | :white\_check\_mark: | alias when grouping with key | | 3 | **date\_format** | string | :x: | format the date data if given key has selected date format column such as created\_at or update (purpose for grouping within Datetime | #### fields specification : (type: list) | NO | KEY | TYPE | REQUIRED | DESC | comment | | -- | ---------------- | ------ | :------------------: | ---------------------------------------------- | --------------------------------------------------------- | | 1 | **key** | string | :x: |

list of grouping keys and its alias

| You may omit **key** if operator is the **count** | | 2 | **name** | string | :white\_check\_mark: | field alias with name | | | 3 | **operator** | string | :white\_check\_mark: | action perform with declared field. | | | 4 | **value** | string | :x: | The value of the declared field | You may omit **value** if operator is not **date** | | 5 | **date\_format** | string | :x: | format of value when it displays | You may omit **date\_format** if operator is not **date** | {% hint style="info" %} * **key** can be **omitted** when operator is 'count' * **value** can be **omitted** when operator is **NOT** 'date' * **date\_format** can be **omitted** when operator is **NOT** 'date' {% endhint %} ***Operators*** | NO | KEY | TYPE | DESC | | -- | -------------- | :----: | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | 1 | count | string | count of grouped items by given key | | 2 | sum | string | sum of grouped items by given key | | 3 | avg | string | average of grouped items by given key | | 4 | max | string | maximum value of grouped items by given key | | 5 | min | string | minimum value of grouped items by given key | | 6 | size | string | get length of list of grouped items by given key | | 7 | add\_to\_set | string |

add items to list
Refer to mongo doc :$addToSet (aggregation)

| | 8 | merge\_objects | string |

merge all objects
Refer to mongo doc : $mergeObjects (aggregation)

| | 9 | date | string | date of specific calculation based on give value with date\_format | ### count specification : *(type: dict)* | NO | KEY | TYPE | REQUIRED | DESC | | ------------ | -------- | ---- | :------------------: | ---------------------------------- | | 1 | **name** | list | :white\_check\_mark: | name(alias) of counts in aggregate | | {% endtab %} | | | | | {% tab title="Filter" %} ```javascript { "query": { "filter": [ { "key": "name", "value": [ "stark", "nick" ], "operator": "in" } ] } } ``` {% hint style="warning" %} **NOTE:** \ \ \&#xNAN;***filter*** and ***filter\_or*** input simultaneously, it performs :point\_right: **`AND`** :point\_left: condition between them. {% endhint %} | NO | KEY | TYPE | REQUIRED | DESC | | -- | -------- | ----------- | :------------------: | -------------------------- | | 1 | key | string | :white\_check\_mark: | any key that to filter out | | 2 | value | list or any | :x: | | | 3 | operator | string | :white\_check\_mark: | refer to operator below | ***Operators refer to*** [***Filter Operators***](/api-reference/common-v1/search-query/filter-options.md) {% endtab %} {% tab title="Filter\_or" %} ```javascript { "query": { "filter_or": [ { "key": "name", "value": [ "stark", "admin" ], "operator": "in" }, { "key": "user_id", "value": [ "ua-abcdefg" ], "operator": "not_in" } ] } } ``` {% hint style="warning" %} **NOTE:** \ \ \&#xNAN;***filter*** and ***filter\_or*** input simultaneously, it performs :point\_right: **`AND`** :point\_left: condition between them. {% endhint %} | KEY | TYPE | REQUIRED | DESC | | -------- | ----------- | :------------------: | -------------------------- | | key | string | :white\_check\_mark: | any key that to filter out | | value | list or any | :x: | | | operator | string | :white\_check\_mark: | refer to operator below | ***Operators refer to*** [***Filter Operators***](/api-reference/common-v1/search-query/filter-options.md) {% endtab %} {% tab title="Sort" %} ```javascript { "query": { "sort": { "name": "resource_count", "desc": true } } } ``` | KEY | TYPE | REQUIRED | DESC | | ---- | ------- | :------------------: | --------------------------------- | | name | string | :white\_check\_mark: | string sorting priority | | desc | boolean | :x: | ascending or descending order Y/N | {% hint style="info" %} **DISTINCT:** In case of distinct, sorting key \ \- **name** is **NOT** available for sorting option\ \- **desc** is available. {% endhint %} {% endtab %} {% tab title="Page" %} ```javascript { "query": { "page": { "start": 1, "limit": 3 } } } ``` | KEY | TYPE | REQUIRED | DESC | | ------------- | ------- | :------: | --------------------------- | | start | integer | :x: | start number | | limit | integer | :x: | number of limit on selected | | {% endtab %} | | | | | {% endtabs %} | | | | --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://spaceone-dev.gitbook.io/api-reference/common-v1/statistics-query.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.