Statistics Query

Basic query for statistics

Basic statistic query format
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"
            }
        }
    }
}
aggregate specification : (type: dict)
NO
KEY
TYPE
REQUIRED
DESC
1
unwind
list
​✅
 (conditional)
set of list to separate items within given paths
for more information at  $unwind (aggregation)​
2
group
list
​✅
 (conditional)
list of grouping keys and its alias
3
count
dict
​✅
 (conditional)
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
unwind specification : (type: list) 
NO
KEY
TYPE
REQUIRED
DESC
1
path
string
​✅
 
path to decomposable item
{
    "unwind": [
        {
            "path": "collection_info.service_accounts"
        }
    ]
}
group specification : (type: dict) 
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"
        }
    ]
}
keys specification : (type: list)
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
fields specification : (type: list)
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
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
​✅
 
name(alias) of counts in aggregate
{
    "query": {
        "filter": [
            {
                "key": "name",
                "value": [
                    "stark",
                    "nick"
                ],
                "operator": "in"
            }
        ]
    }
}
NOTE: 

filter and filter_or input simultaneously, it performs 👉
 AND 👈
 condition between them.
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
Operators refer to Filter Operators​
{
    "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 👉
 AND 👈
 condition between them.
KEY
TYPE
REQUIRED
DESC
key
string
​✅
 
any key that to filter out
value
list or any
​❌
​
​
operator
string
​✅
 
refer to operator below
Operators refer to Filter Operators​
{
    "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
​

Filter Operators

Ex.1 case: Project

Last modified 3yr ago

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

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