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.

KEY

TYPE

REQUIRED

DESC

distinct

list

✅

grouping in distinct condition with given items

aggregate

list

✅

grouping and set fields for grouping

filter

list

❌

filter out within operator

filter_or

list

❌

filter out within operator

sort

dict

❌

sorting a list with given keys

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

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

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

KEY

TYPE

REQUIRED

DESC

unwind

list

✅ (conditional)

set of list to separate items within given paths

for more information at $unwind (aggregation)

group

list

✅ (conditional)

list of grouping keys and its alias

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)

KEY

TYPE

REQUIRED

DESC

path

string

✅

path to decomposable item

{
    "unwind": [
        {
            "path": "collection_info.service_accounts"
        }
    ]
}

group specification : (type: dict)

KEY

TYPE

REQUIRED

DESC

keys

list

✅

list of grouping keys and its alias

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)

KEY

TYPE

REQUIRED

DESC

key

string

✅

key for grouping within declared resource type

name

string

✅

alias when grouping with key

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)

KEY

TYPE

REQUIRED

DESC

comment

key

string

❌

list of grouping keys and its alias

You may omit key if operator is the count

name

string

✅

field alias with name

operator

string

✅

action perform with declared field.

value

string

❌

The value of the declared field

You may omit value if operator is not date

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

KEY

TYPE

DESC

count

string

count of grouped items by given key

sum

string

sum of grouped items by given key

avg

string

average of grouped items by given key

max

string

maximum value of grouped items by given key

min

string

minimum value of grouped items by given key

size

string

get length of list of grouped items by given key

add_to_set

string

add items to list Refer to mongo doc :$addToSet (aggregation)

merge_objects

string

merge all objects Refer to mongo doc : $mergeObjects (aggregation)

date

string

date of specific calculation based on give value with date_format

count specification : (type: dict)

KEY

TYPE

REQUIRED

DESC

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.

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

PreviousFilter Operators NextEx.1 case: Project

Last updated 5 years ago

Was this helpful?