Statistics Query
Basic query for statistics
query: query includes following options items below for grouping.
Note: Either of distinct option or aggregate option must be given.
NO | KEY | TYPE | REQUIRED | DESC |
1 | distinct | list | ✅ | grouping in distinct condition with given items |
2 | aggregate | list | ✅ | grouping and set fields for grouping |
3 | filter | list | ❌ | filter out within operator |
4 | filter_or | list | ❌ | filter out within operator |
5 | sort | dict | ❌ | sorting a list with given keys |
6 | page | dict | ❌ | set limit and its start page in grouping query to display |
FILTER:
filter performs within AND condition
<filter> and <filter> and <filter> ...
👉
FILTER_OR:
filter_or performs within OR condition
<filter> or <filter> or <filter> ...
👉
SKELETON
Distinct
Aggregate
Filter
Filter_or
Sort
Page
{
"query": {
"distinct": ...,
"aggregate": {...},
"filter": [...],
"filter_or": [...],
"sort": {...},
"limit":{...}
}
}
{
"query": {
"distinct": "server_id"
}
}{
"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"
}
}
}
}
NO | KEY | TYPE | REQUIRED | DESC |
1 | unwind | list | ✅ | set of list to separate items within given paths |
2 | group | list | ✅ | list of grouping keys and its alias |
3 | count | dict | ✅ | count of grouping results with given name |
Either of 'group' or 'unwind' or 'count' must declare under aggregate parameters
Note: Applying order on aggregate
unwind > group > count
NO | KEY | TYPE | REQUIRED | DESC |
1 | path | string | ✅ | path to decomposable item |
{
"unwind": [
{
"path": "collection_info.service_accounts"
}
]
}
NO | KEY | TYPE | REQUIRED | DESC |
1 | keys | list | ✅ | list of grouping keys and its alias |
2 | fields | list | ❌ | fields to express each alias with operators |
{
"keys": [
{
"key": "service_account_id",
"name": "service_account"
}
],
"fields": [
{
"operator": "count",
"name": "cloud_service_count"
}
]
}
{
"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"
}
]
}
Note: each items in keys applies to given name(alias)
NO | KEY | TYPE | REQUIRED | DESC |
1 | key | string | ✅ | key for grouping within declared resource type |
2 | name | string | ✅ | alias when grouping with key |
3 | date_format | string | ❌ | format the date data if given key has selected date format column such as created_at or update (purpose for grouping within Datetime |
NO | KEY | TYPE | REQUIRED | DESC | comment |
1 | key | string | ❌ | list of grouping keys and its alias
| You may omit key if operator is the count |
2 | name | string | ✅ | field alias with name | |
3 | operator | string | ✅ | action perform with declared field. | |
4 | value | string | ❌ | The value of the declared field | You may omit value if operator is not date |
5 | date_format | string | ❌ | format of value when it displays | You may omit date_format if operator is not date |
- 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'
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 | |
8 | merge_objects | string | |
9 | date | string | date of specific calculation based on give value with date_format |
NO | KEY | TYPE | REQUIRED | DESC |
1 | name | list | ✅ | name(alias) of counts in aggregate |
{
"query": {
"filter": [
{
"key": "name",
"value": [
"stark",
"nick"
],
"operator": "in"
}
]
}
}
NOTE:
filter and filter_or input simultaneously, it performs condition between them.
👉
AND
👈
NO | KEY | TYPE | REQUIRED | DESC |
1 | key | string | ✅ | any key that to filter out |
2 | value | list or any | ❌ | |
3 | operator | string | ✅ | refer to operator below |
{
"query": {
"filter_or": [
{
"key": "name",
"value": [
"stark",
"admin"
],
"operator": "in"
},
{
"key": "user_id",
"value": [
"ua-abcdefg"
],
"operator": "not_in"
}
]
}
}
NOTE:
filter and filter_or input simultaneously, it performs condition between them.
👉
AND
👈
KEY | TYPE | REQUIRED | DESC |
key | string | ✅ | any key that to filter out |
value | list or any | ❌ | |
operator | string | ✅ | refer to operator below |
{
"query": {
"sort": {
"name": "resource_count",
"desc": true
}
}
}
KEY | TYPE | REQUIRED | DESC |
name | string | ✅ | string sorting priority |
desc | boolean | ❌ | ascending or descending order Y/N |
DISTINCT:
In case of distinct, sorting key
- name is NOT available for sorting option
- desc is available.
{
"query": {
"page": {
"start": 1,
"limit": 3
}
}
}
KEY | TYPE | REQUIRED | DESC |
start | integer | ❌ | start number |
limit | integer | ❌ | number of limit on selected |
Last modified 3yr ago