Skip to content

Auto generated schema based AGGREGATE API
[Mongo Database Only]

Table structure

customer_id first_name last_name last_update pincode isActive
1 Bob lin 2022-11-14 04: 34: 58 382345 1
2 Alice Page 2022-10-15 02: 10: 40 382346 1
3 Mallory Brown 2022-09-13 03: 44: 05 382347 1
4 Eve Mathly 2022-11-12 01: 59: 33 382348 1
5 Eve Page 2022-11-12 01: 59: 33 382349 1

Request Method: POST

Basic Aggregation

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$group": {
            "_id": "$_id",
            "total": {
                "$sum": "$customer_id"
            }
        }
    },
    {
        "$match": {
            "total": {
                "$gt": 1
            }
        }
    },
    {
        "$sort": {
            "total": -1
        }
    },
    {
        "$count": "CountValue"
    }
]

$project stage

  • It will return documents/rows where the first_name field is a small case.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "full_name": {
                "$toLower": "$first_name"
            }
        }
    }
]

$addFields

  • It will return documents/rows with a new virtual column "full_name" with concat first_name and last_name field data.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$addFields": {
            "full_name": {
                "$concat": [
                    "$first_name",
                    " ",
                    "$last_name"
                ]
            }
        }
    }
]

$bucket

  • It will return documents/rows where we can group products with a specific price range with a total number of product counts in that range.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$bucket": {
            "groupBy": "$price",
            "boundaries": [
                10000,
                20000,
                30000,
                40000,
                50000
            ],
            "default": "Other",
            "output": {
                "count": {
                    "$sum": 1
                },
                "product_details": {
                    "$push": {
                        "name": "$name",
                        "price": "$price"
                    }
                }
            }
        }
    }
]

$facet with multiple $bucket

  • It will return documents/rows where,

    • we can group products with a specific price range with a total number of product counts in that price.
    • we can group products with a specific category range with a total number of product counts in that category.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$facet": {
            "price": [
                {
                    "$bucket": {
                        "groupBy": "$price",
                        "boundaries": [
                            10000,
                            20000,
                            30000,
                            40000,
                            50000,
                            60000,
                            70000,
                            80000
                        ],
                        "default": "Other",
                        "output": {
                            "count": {
                                "$sum": 1
                            },
                            "product_details": {
                                "$push": {
                                    "name": "$name",
                                    "price": "$price"
                                }
                            }
                        }
                    }
                }
            ],
            "categories": [
                {
                    "$bucket": {
                        "groupBy": "$category_id",
                        "boundaries": [
                            10000,
                            20000,
                            30000,
                            40000,
                            50000,
                            60000,
                            70000,
                            80000
                        ],
                        "default": "Other",
                        "output": {
                            "count": {
                                "$sum": 1
                            },
                            "product_details": {
                                "$push": {
                                    "name": "$name",
                                    "id": "$id",
                                    "category_id": "$category_id"
                                }
                            }
                        }
                    }
                }
            ]
        }
    }
]

$collStats, latencyStats > histograms, storageStats, count

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$collStats": {
            "latencyStats": {
                "histograms": true
            },
            "storageStats": {},
            "count": {}
        }
    }
]

$count

  • It will return documents/rows count where the price is greater than "10000"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$match": {
            "price": {
                "$gt": 10000
            }
        }
    },
    {
        "$count": "price_gt_10000"
    }
]

$limit

  • It will return only the first two documents/rows.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$limit": 2
    }
]

$lookup

  • It will return documents/rows of products nested with customer data.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$lookup": {
            "from": "products",
            "localField": "customer_id",
            "foreignField": "owner_id",
            "as": "customer_data"
        }
    }
]

$match

  • It will return documents/rows where first_name match with "Bob"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$match": {
            "first_name": "Bob"
        }
    }
]

$redact, $cond, if, then, else, $$DESCEND, $$PRUNE

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$redact": {
            "$cond": {
                "if": {
                    "$eq": [
                        "$first_name",
                        "JOHNNY"
                    ]
                },
                "then": "$$DESCEND",
                "else": "$$PRUNE"
            }
        }
    }
]

$replaceRoot, newRoot

  • It will return a new virtual column with the name "full_name" with the concatenation of two columns first_name and last_name

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$replaceRoot": {
            "newRoot": {
                "full_name": {
                    "$concat": [
                        "$first_name",
                        " ",
                        "$last_name"
                    ]
                }
            }
        }
    }
]

$sample

  • It will return the first two samples of documents/rows.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$sample": {
            "size": 2
        }
    }
]

$skip

  • It will skip the first two records and return documents/rows.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$skip": 2
    }
]

$sort

  • Sorting value -1 gives a response in descending order and 1 in ascending order.
  • The below payload will sort data in ascending order.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$sort": {
            "customer_id": 1
        }
    }
]

  • It will sort data in descending order.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$sort": {
            "customer_id": -1
        }
    }
]

$unwind, $sortByCount

  • It will first unwind the data from JSON to node ages and then sort the data.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$unwind": "$ages"
    },
    {
        "$sortByCount": "$ages"
    }
]

$abs, $subtract

URL - It will subtract tax amount from total amount and return value in "Grand_Total"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "Grand_Total": {
                "$abs": {
                    "$subtract": [
                        "$total",
                        "$tax"
                    ]
                }
            }
        }
    }
]

$add

  • It will add tax amount into total amount and return value in "Grand_Total"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "Grand_Total": {
                "$add": [
                    "$price",
                    "$category_id"
                ]
            }
        }
    }
]

$ceil, $floor

  • ceil: If the value is decimal then round the price, adding with +1
  • floor: If the value is decimal then round the price, remove the decimal

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "ceilingValue": {
                "$ceil": "$price"
            },
            "floorValue": {
                "$floor": "$price"
            },
            "id": 1
        }
    }
]

$divide

  • It will return a value divided by 100.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "pricePercentage": {
                "$divide": [
                    "$price",
                    100
                ]
            },
            "id": 1
        }
    }
]

$multiply

  • It will return "Grand_Total" after multiplying the price by quantity.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "Grand_Total": {
                "$multiply": [
                    "$price",
                    "$quantity"
                ]
            },
            "id": 1
        }
    }
]

$arrayElemAt

  • It will return the color of index 1 of attributes.color array.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "first": {
                "$arrayElemAt": [
                    "$attributes.color",
                    1
                ]
            }
        }
    }
]

$filter

  • It will filter data with age greater than 50

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "ages": {
                "$filter": {
                    "input": "$ages",
                    "as": "age",
                    "cond": {
                        "$gte": [
                            "$$age.age",
                            50
                        ]
                    }
                }
            }
        }
    }
]

$in

  • It will return documents/rows where hobbies equal to "traveling"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "customer_id": "$customer_id",
            "TravelingHobby": {
                "$in": [
                    "traveling",
                    "$hobbies"
                ]
            }
        }
    }
]

$indexOfArray

  • It will return the index of "ages.birth_year = 1971" from the array.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "customer_id": "$customer_id",
            "index": {
                "$indexOfArray": [
                    "$ages.birth_year",
                    1971
                ]
            }
        }
    }
]

$isArray

  • It will return documents/rows which fulfill the condition.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "isHobbyCricket": {
                "$cond": {
                    "if": {
                        "$isArray": "$hobbies"
                    },
                    "then": {
                        "$in": [
                            "traveling",
                            "$hobbies"
                        ]
                    },
                    "else": "One or more fields is not an array."
                }
            }
        }
    }
]

$map

  • It will return documents/rows with only integer value from distance field value, and truncate the decimal value from distance.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "integerValues": {
                "$map": {
                    "input": "$distances",
                    "as": "decimalValue",
                    "in": {
                        "$trunc": "$$decimalValue"
                    }
                }
            }
        }
    }
]

$reverseArray

  • It will return the reverse value of the array.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "reverseDistances": {
                "$reverseArray": "$distances"
            }
        }
    }
]

$size

  • It will return the size of the array as per the condition.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "customer_id": 1,
            "address": 1,
            "numberOfHobbies": {
                "$cond": {
                    "if": {
                        "$isArray": "$hobbies"
                    },
                    "then": {
                        "$size": "$hobbies"
                    },
                    "else": "NA"
                }
            }
        }
    }
]

$and

  • It will return documents/rows, and the result equals true where the price is greater than 60000 and category_id less than 45000.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": "$id",
            "result": {
                "$and": [
                    {
                        "$gt": [
                            "$price",
                            60000
                        ]
                    },
                    {
                        "$lt": [
                            "$category_id",
                            45000
                        ]
                    }
                ]
            }
        }
    }
]

$not

  • It will return documents/rows, the result equals false where the price is greater than 30000.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": "$id",
            "result": {
                "$not": [
                    {
                        "$gt": [
                            "$price",
                            30000
                        ]
                    }
                ]
            }
        }
    }
]

$or

  • It will return documents/rows, and the result equals true where the price is greater than 60000 or category_id less than 1000.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": "$id",
            "result": {
                "$or": [
                    {
                        "$gt": [
                            "$price",
                            60000
                        ]
                    },
                    {
                        "$lt": [
                            "$category_id",
                            1000
                        ]
                    }
                ]
            }
        }
    }
]

$cmp

  • It will return documents/rows, "comparePrice equals to 1" where price greater than 42187 else "comparePrice equals to -1"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "comparePrice": {
                "$cmp": [
                    "$price",
                    42187
                ]
            },
            "_id": 1
        }
    }
]

$eq

  • It will return documents/rows, "equalPrice equals to true" where price equals 42187 else "equalPrice equals to false"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "equalPrice": {
                "$eq": [
                    "$price",
                    42187
                ]
            },
            "_id": 0
        }
    }
]

$gt

  • It will return documents/rows, "graterPrice equals to true" where price grater then 42187 else "graterPrice equals to false"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "graterPrice": {
                "$gt": [
                    "$price",
                    42187
                ]
            },
            "_id": 0
        }
    }
]

$gte

  • It will return documents/rows, "gePrice equals to true" where price grater then or equals to 42187 else "gePrice equals to false"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "gePrice": {
                "$gte": [
                    "$price",
                    42187
                ]
            },
            "_id": 0
        }
    }
]

$lt

  • It will return documents/rows, "lessPrice equals to true" where price less then 42187 else "lessPrice equals to false"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "lessPrice": {
                "$lt": [
                    "$price",
                    42187
                ]
            },
            "_id": 0
        }
    }
]

$lte

  • It will return documents/rows, "ltePrice equals to true" where price less than equals to 42187 else "ltePrice equals to false"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "ltePrice": {
                "$lte": [
                    "$price",
                    42187
                ]
            },
            "_id": 0
        }
    }
]

$ne

  • It will return documents/rows, "nePrice equals to true" where price not equals to 42187 else "nePrice equals to false"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "nePrice": {
                "$ne": [
                    "$price",
                    42187
                ]
            },
            "_id": 0
        }
    }
]

$cond

  • It will return documents/rows, where price grater than 50000 discount apply 30% else 10%

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "price": 1,
            "discount": {
                "$cond": {
                    "if": {
                        "$gt": [
                            "$price",
                            50000
                        ]
                    },
                    "then": 30,
                    "else": 10
                }
            },
            "_id": 0
        }
    }
]

$ifNull

  • It will return documents/rows, if shipping object is available then return in response else return null

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "price": 1,
            "shipping": {
                "$ifNull": [
                    "$shipping",
                    null
                ]
            },
            "_id": 0
        }
    }
]

$switch

  • It will return documents/rows, if "price equals 50000 = equals" else if "price grater then 50000 = grater than" else if "price less than 50000 = less than"

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "price": 1,
            "priceSummary": {
                "$switch": {
                    "branches": [
                        {
                            "case": {
                                "$eq": [
                                    "$price",
                                    50000
                                ]
                            },
                            "then": "equals"
                        },
                        {
                            "case": {
                                "$gt": [
                                    "$price",
                                    50000
                                ]
                            },
                            "then": "greater than"
                        },
                        {
                            "case": {
                                "$lt": [
                                    "$price",
                                    50000
                                ]
                            },
                            "then": "less than"
                        }
                    ]
                }
            }
        }
    }
]

$concat

  • It will return documents/rows, with concat "name" + "_" + "description" = name_Description.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$project": {
            "id": 1,
            "name_Description": {
                "$concat": [
                    "$name",
                    "_",
                    "$description"
                ]
            }
        }
    }
]

$max

  • It will return documents/rows, with concat "name" + "_" + "description" = name_Description.

URL

/api/schema/user-path/instance/database/table/aggregate
Request Payload
[
    {
        "$group": {
            "_id": "$price",
            "maxQuantity": {
                "$max": "$quantity"
            }
        }
    }
]

Supported headers

Header Description
x-am-response-case It will change the response keys text as requested case.
"x-am-response-case": "camelCase"
noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
x-am-response-object-type To get flat response we use this request header.
"x-am-response-object-type": "make_flat"
no_action | make_flat
x-am-meta To get the meta-data of requested API.
"x-am-meta": "true"
false | true
x-am-secret Place secret id from API Maker.
"x-am-secret": "6381b80359bdbd3a87c9abd5"
x-am-internationalization We can get backend error messages in any user language and directly show them to the user in UI, so the user can take appropriate actions.
Provide saved internationalization name in request header.
"x-am-internationalization": "HINDI"
x-am-run-in-sandbox System will try to run requests in maximum provided sandbox, so if 1 provided every request will run in one sandbox, even if we have multiple sandbox with multiple API Maker instances.
"x-am-run-in-sandbox": "0"
x-am-content-type-response We can provide response type in request header. As provided header value we can expect the response type.
"x-am-content-type-response": "application/json"
application/json | text/xml | text/yaml
x-am-cache-control To reset the cache of the requested API manually and get fresh data, we can use 'x-am-cache-control' request header.
"x-am-cache-control": "reset_cache"
no_action | reset_cache
x-am-get-encrypted-data Encrypt response data and get in to the response.
"x-am-get-encrypted-data": "get_data_and_encryption"
no_encryption | get_only_encryption | get_data_and_encryption
x-am-authorization Provide token of API user in "x-am-authorization" header which will be generated from API Users inside API Maker.
"x-am-authorization": "eyJhbGciOiJIUzI1NiIsdlRrblJlcS"
x-am-user-authorization User token should be provided in 'x-am-user-authorization' header which will be generated based on some database user if required.
"x-am-user-authorization": "eyJhbGciOiJIpc1BydlRrblJlcS"
x-aws-authorization Provide AWS Cognito token in request header 'x-aws-authorization', if required.
"x-aws-authorization": "eyJhbGciOiJIUc1BdlRrlJlcS"
x-google-authorization Provide Google user token in request header 'x-google-authorization', if required.
"x-google-authorization": "eyJhbGceyJpc1BdlRrlJlcS"
x-azure-authorization Provide Azure active directory token in request header 'x-azure-authorization', if required.
"x-azure-authorization": "eyJhbGciOiJIUzI1NiIJpc1BdlRrlJlcS"
x-no-compression If user do not send 'x-no-compression' in request header, and response characters are more than value of "maxCharsResToCache" than the response will be compressed.
"x-no-compression": "true"
x-am-sandbox-timeout If any API did not give a response within given time, the sandbox will break and give a proper error message in the response.
"x-am-sandbox-timeout": "13000"  // milliseconds
x-am-encrypted-payload When user sent encrypted payload, user must have to sent "x-am-encrypted-payload:true".
"x-am-encrypted-payload": "true"