> For the complete documentation index, see [llms.txt](https://spaceone-dev.gitbook.io/api-reference/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://spaceone-dev.gitbook.io/api-reference/common-v1/statistics-query.md).

# 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  \
&#x20;:point\_right: \<filter> and \<filter> and \<filter> ...
{% endhint %}

{% hint style="info" %}
**FILTER\_OR:**&#x20;

*filter\_or* performs within **OR**  condition  \
&#x20;:point\_right:  \<filter> or \<filter> or \<filter> ...
{% 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) | <p>set of list to separate items within given paths</p><p>for more information at  <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/index.html">$unwind (aggregation)</a></p> |
| 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)*&#x20;

| 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)*&#x20;

| 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:         | <p>list of grouping keys and its alias<br></p> | 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 | <p>add items to list <br>Refer to mongo doc :<a href="https://docs.mongodb.com/manual/reference/operator/aggregation/addToSet/">$addToSet (aggregation) </a> </p>        |
| 8  | merge\_objects | string | <p>merge all objects <br>Refer to mongo doc : <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/mergeObjects/">$mergeObjects (aggregation)</a></p> |
| 9  | date           | string | date of specific calculation based on give value with date\_format                                                                                                       |

### count specification : *(type: dict)*&#x20;

| 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:** \
\
***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:** \
\
***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:**&#x20;

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 %} |         |          |                             |