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

{
    "query": {
        "distinct": ...,
        "aggregate": {...},
        "filter": [...],
        "filter_or": [...],
        "sort": {...},
        "limit":{...}
    }
}

Distinct

{
    "query": {
        "distinct": "server_id"
    }
}{

Aggregate

    "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

Filter

{
    "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

Filter_or

{
    "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

Sort

{
    "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.

Page

{
    "query": {
        "page": {
            "start": 1,
            "limit": 3
        }
    }
}

KEY	TYPE	REQUIRED	DESC
start	integer	❌	start number
limit	integer	❌	number of limit on selected