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