Skip to content

Query-params

Table structure

Customers table

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 header on "Get all api"

  • Request Method: GET

find

'find' is used to match particular values of keys.

URL

  • Schema based API url 

    /api/schema/user-path/instance/database/table?find={first_name:'Bob'}

  • schemaless API url 

    /api/gen/user-path/instance/database/table?find={first_name:'Bob'}


{
    "find": {
        "customer_id": 2
    }
}

$lt operation

  • Find all data which has customer_id less than 2.
{
    "find": {
        "customer_id": {
            "$lt": 2
        }
    }
}

$gt operation

  • Find all data which has customer_id greater than 2.
{
    "find": {
        "customer_id": {
            "$gt": 2
        }
    }
}

$lte operation

  • Find all data which has customer_id less than or equal 2.
{
    "find": {
        "customer_id": {
            "$lte": 2
        }
    }
}

$gte operation

  • Find all data which has customer_id greater than or equal 2.
{
    "find": {
        "customer_id": {
            "$gte": 2
        }
    }
}

$eq operation

  • Find data which has customer_id equal 2.
{
    "find": {
        "customer_id": {
            "$eq": 2
        }
    }
}

$ne operation

  • Find all data which has customer_id not equal 2.
{
    "find": {
        "customer_id": {
            "$ne": 2
        }
    }
}

$not operator

  • Find all data which has owner_id is not in the given numbers of the array.
{
    "find": {
        "owner_id": {
            "$not": {
                "$in": [1, 2, 3, 4, 5, 6, 7, 8, 9]
            }
        }
    }
}

$and operation

  • Find all data which has customer_id equal to 1 and pincode equal to 382345.
{
    "find": {
        "$and": [
            {
                "customer_id": 1
            },
            {
                "pincode": 382345
            }
        ]
    }
}

$or operation

  • Find all data which has customer_id equal to 1 or pincode equal to 382345.
{
    "find": {
        "$or": [
            {
                "customer_id": 1
            },
            {
                "pincode": 382345
            }
        ]
    }
}

$nin operation

  • Find all data which has customer_id should not be in the given array values.
{
    "find": {
        "customer_id": {
            "$nin": [2, 3, 4]
        }
    }
}

$in operation

  • Find all data which has owner_id value match with given array values.
{
    "find": {
        "owner_id": {
            "$in": [1, 2]
        }
    }
}

$like operation in Custom api

  • Find all data which has first_name start match with 'Bob'.
{
    "find": {
        "first_name": {
            "$like": "Bob%"
        }
    }
}

find-join (nested find)

  • Match with deep data. The 'owner_id' and 'customer_id' both are from different collections.
{
    "find": {
        "owner_id.customer_id": 2
    }
}

  • With $and operator 

    {
    "find": {
        "$and": [
            { "first_name.customer_id.customer_id": 1 },
            { "first_name.customer_id.shipping_id": 1 }
        ]
    },
    "deep": "first_name, first_name.customer_id"
}

  • With $or operator 

    {
    "find": {
        "$or": [
            { "first_name.customer_id.customer_id": 1 },
            { "first_name.customer_id.shipping_id": 2 }
        ]
    },
    "limit": 2
}

Give string value in number field

  • customer_id has a type number. If the user provides a number in a double quote it will give the correct response.
{
    "find": {
        "customer_id": "8"
    }
}

$regex support

  • Find all data which has first_name start with the 'Sie' string.
{
    "find": {
        "first_name": {
            "$regex": "Sie%"
        }
    }
}

skip

  • Skip records from the expected response and got the rest of the record in the response.

URL

  • Schema based API url 

    /api/schema/user-path/instance/database/table?skip=2

  • schemaless API url 

    /api/gen/user-path/instance/database/table?skip=2


{
    "data": [
        {
            "customer_id": 3,
            "first_name": "Mallory",
            "last_name": "Brown"
        },
        {
            "customer_id": 4,
            "first_name": "Eve",
            "last_name": "Mathly"
        },
        {
            "customer_id": 5,
            "first_name": "Eve",
            "last_name": "Page"
        }
    ]
}

limit

  • To get limited data objects user should provide a 'limit' in the request.
  • limit takes integer number values.

URL

With the below request, the user only gets 2 data objects in the response.

  • Schema based API url 

    /api/schema/user-path/instance/database/table?limit=2

  • schemaless API url 

    /api/gen/user-path/instance/database/table?limit=2

Got only two records in response.

{
    "data": [
        {
            "customer_id": 1,
            "first_name": "Bob",
            "last_name": "Lin"
        },
        {
            "customer_id": 2,
            "first_name": "Alice",
            "last_name": "Page"
        }
    ]
}

sort

  • To change response order with a particular column name in ascending or descending order.

Ascending order

Change response order with customer_id ascending order value.

URL

  • Schema based API url 

    /api/schema/user-path/instance/database/table?sort=customer_id

  • schemaless API url 

    /api/gen/user-path/instance/database/table?sort=customer_id


{
    "data": [
        {
            "customer_id": 1,
            "first_name": "Bob",
            "last_name": "Lin"
        },
        {
            "customer_id": 2,
            "first_name": "Alice",
            "last_name": "Page"
        },
        {
            "customer_id": 3,
            "first_name": "Mallory",
            "last_name": "Brown"
        },
        {
            "customer_id": 4,
            "first_name": "Eve",
            "last_name": "Mathly"
        },
        {
            "customer_id": 5,
            "first_name": "Eve",
            "last_name": "Page"
        }
    ]
}

Descending order

Change response order with customer_id descending order value.

URL

/api/gen/user-path/instance/database/table?sort=-customer_id

{
    "data": [
        {
            "customer_id": 5,
            "first_name": "Eve",
            "last_name": "Page"
        },
        {
            "customer_id": 4,
            "first_name": "Eve",
            "last_name": "Mathly"
        },
        {
            "customer_id": 3,
            "first_name": "Mallory",
            "last_name": "Brown"
        },
        {
            "customer_id": 2,
            "first_name": "Alice",
            "last_name": "Page"
        },
        {
            "customer_id": 1,
            "first_name": "Bob",
            "last_name": "Lin"
        }
    ]   
}

select

  • To get specific columns in response, the user will provide a 'select' param.

URL

  • Schema based API url 

    /api/schema/user-path/instance/database/table?select=first_name,last_name

  • schemaless API url 

    /api/gen/user-path/instance/database/table?select=first_name,last_name

  • Got only first_name, last_name, and primary key fields in the response.

{
    "data": [
        {
            "customer_id": 1,
            "first_name": "Bob",
            "last_name": "Lin"
        },
        {
            "customer_id": 2,
            "first_name": "Alice",
            "last_name": "Page"
        },
        {
            "customer_id": 3,
            "first_name": "Mallory",
            "last_name": "Brown"
        },
        {
            "customer_id": 4,
            "first_name": "Eve",
            "last_name": "Mathly"
        },
        {
            "customer_id": 5,
            "first_name": "Eve",
            "last_name": "Page"
        }
    ]   
}

Negative value

  • Use '-' before the column name and remove that column from the response.
/api/gen/user-path/instance/database/table?select=-first_name

deep

  • 'deep' is used to get inherited data.
  • Get the inherited data from different databases, and instances.
  • Multiple-level inheritance can be supported.

cities table in 'postgresql' instance

id state_id city_name
101 201 AHMEDABAD
102 201 RAJKOT
103 201 SURAT
104 201 VADODARA
105 202 KOCHI
106 202 KANNUR
107 202 PONNANI
108 203 AMRITSAR
109 203 MOHALI
110 204 DUNCAN

states table in 'oracle' instance

id country_id state_name
201 301 GUJARAT
202 301 KERALA
203 301 PUNJAB
204 302 ARIZONA

countries table in 'mysql' instance

id country_name
301 INDIA
302 USA

URL

  • Schema based API url 

    /api/schema/user-path/instance/database/table?deep=[{s_key:"state_id",t_instance:"oracle",t_db:"inventory",t_col:"states",t_key:"id"}]

  • schemaless API url 

    /api/gen/user-path/instance/database/table?deep=[{s_key:"state_id",t_instance:"oracle",t_db:"inventory",t_col:"states",t_key:"id"}]

Single level deep

  • 's_key' = source key. If we hit request to the 'cities' table and try to deep in the 'state_id' column. The 'state_id' will be the source key name.
  • 't_instance' = target instance. The 'states' table is in the 'oracle' instance.
  • 't_db' = target database. The 'states' table is in the 'inventory' database.
  • 't_col' = target collection. It's 'states'. We try to get deep 'states' table data.
  • 't_key' = target key. The column name of the 'states' table which we will try to match with the 'state_id' column in the 'cities' table data.
  • 'find' is used to find a particular match.
  • 'isMultiple' is used when multiple matches are available and want to get in response all matches.
  • 'limit', get only that number of data objects in the response.
  • 'select', comma separated columns name. Only mentioned columns get in response.
  • 'skip', skip given number objects from the start.
  • 'sort', sort response objects in ascending order with a given column name. If it's '-state_name' then it will set the order descending with the given column name.
deep: [
    {
        s_key: "state_id",
        t_instance: "oracle",
        t_db: "inventory",
        t_col: "states",
        t_key: "id",
        find: {
            "state_name": "GUJARAT"
        },
        isMultiple: true,
        limit: 10,
        select: "state_name, state_id",
        skip: 2,
        sort: "state_name",
    }
]

Multi level deep

  • Get multi level data from different databases, instances.
deep: [
    {
        s_key: "SOURCE_COLLECTION_COLUMN_NAME",
        t_instance: "TARGET_INSTANCE_NAME",
        t_db: "TARGET_DATABASE_NAME",
        t_col: "TARGET_COLLECTION_NAME",
        t_key: "TARGET_COLLECTION_COLUMN_NAME",
        deep: [
            {
                s_key: "SOURCE_COLLECTION_COLUMN_NAME",
                t_instance: "TARGET_INSTANCE_NAME",
                t_db: "TARGET_DATABASE_NAME",
                t_col: "TARGET_COLLECTION_NAME",
                t_key: "TARGET_COLLECTION_COLUMN_NAME",
                find: {
                    "COLUMN_NAME": "COLUMN_VALUE"
                },
                select: "COMMA_SEPARATED_COLUMN_NAMES",
                deep: [
                    {
                        s_key: "SOURCE_COLLECTION_COLUMN_NAME",
                        t_instance: "TARGET_INSTANCE_NAME",
                        t_db: "TARGET_DATABASE_NAME",
                        t_col: "TARGET_COLLECTION_NAME",
                        t_key: "TARGET_COLLECTION_COLUMN_NAME",
                        limit: 1
                    }
                ]
            }
        ]
    }
]

Deep populate from two tables at a time

  • Get deep data from multiple tables in different source keys at a time.
{
    "deep": [
        {
            "s_key": "SOURCE_COLLECTION_COLUMN_NAME",
            "t_instance": "TARGET_INSTANCE_NAME",
            "t_db": "TARGET_DATABASE_NAME",
            "t_col": "TARGET_COLLECTION_NAME",
            "t_key": "TARGET_COLLECTION_COLUMN_NAME"
        },
        {
            "s_key": "SOURCE_COLLECTION_COLUMN_NAME_1",
            "t_instance": "TARGET_INSTANCE_NAME_1",
            "t_db": "TARGET_DATABASE_NAME_1",
            "t_col": "TARGET_COLLECTION_NAME_1",
            "t_key": "TARGET_COLLECTION_COLUMN_NAME_1"
        }
    ]
}

Example

  • Let countries, states, and cities collection in different instances and databases.

  • Query to get multi deep data.

  • Here, we hit the below query on the 'cities' collection. Which is in 'postgresql'.
  • The 'states' table is in the 'oracle' instance and the 'countries' table is in the 'mysql' instance.
  • limit:1, so we will get only one object in response.
{
    "find": {},
    "limit": 1,
    "deep": [
        {
            "s_key": "state_id",
            "t_instance": "oracle",
            "t_db": "inventory",
            "t_col": "states",
            "t_key": "id",
            "deep": [
                {
                    "s_key": "country_id",
                    "t_instance": "mysql",
                    "t_db": "inventory",
                    "t_col": "countries",
                    "t_key": "id"
                }
            ]
        }
    ]
}
  • Response of above query.
{
    "success": true,
    "statusCode": 200,
    "data": [
        {
            "id": 101,
            "state_id": {
                "id": 201,
                "country_id": {
                    "id": 301,
                    "country_name": "INDIA"
                },
                "state_name": "GUJARAT"
            },
            "city_name": "AHMEDABAD"
        }
    ]
}

"getTotalCount"

  • To get a total number of data objects in response we should pass the 'getTotalCount' value as a boolean.
  • If getTotalCount is true in the request, the response will get a total number of data objects.
  • By default its value is false.
  • The total number of data objects gets in the 'totalCount' key.

URL

  • Schema based API url 

    /api/schema/user-path/instance/database/table?getTotalCount=true

  • schemaless API url 

    /api/gen/user-path/instance/database/table?getTotalCount=true

Got a response with a number of record counts.

{
    "data": [
        {
            "customer_id": 1,
            "first_name": "Bob",
            "last_name": "Lin"
        },
        {
            "customer_id": 2,
            "first_name": "Alice",
            "last_name": "Page"
        }
    ],
    "totalCount": 2
}

getTotalCount in body

  • In the query for get data and query stream API we can provide 'getTotalCount' in body.
  • It will get response with the response data total count.

Request payload 

{
    "find": {
        "first_name": "Wayne"
    },
    "getTotalCount": true
}

Response 

{
    "success": true,
    "statusCode": 200,
    "data": [
        {
            "_id": "63e06907bd0e063920533baf",
            "customer_id": 82959,
            "first_name": "Wayne",
            "last_name": "Brown",
            "phone": 9683803357
        }
    ],
    "totalCount": 1
}

upsert

  • Specially for Update by id
  • Key = upsert, Value = false 
    upsert = false, it mean if id is available then record is update as per body.
  • Key = upsert, Value = true 
    upsert = true, either if id is available then record update or record insert as per body.
"data": [
    {
        "first_name": "John",
        "last_name": "Sina",
        "last_update": "2022-11-14 04: 34: 58",
        "pincode": 382350,
        "isActive": 0
    }
]

"returnDocument"

  • Specially for Update by id
  • after:- you got current data in response [response with updated].
  • Key = returnDocument, Value = after 
    "data": [
    {
        "customer_id": 1,
        "first_name": "Bob",
        "last_name": "Lin",
        "last_update": "2022-11-14 04: 34: 58",
        "pincode": 382330,
        "isActive": 1
    },
    .
    .
    .
    {
        "customer_id": 6,
        "first_name": "John",
        "last_name": "Sina",
        "last_update": "2022-11-14 04: 34: 58",
        "pincode": 382350,
        "isActive": 0
    }
]
  • before:- you got previous data in response [response without updated].
  • Key = returnDocument, Value = before
"data": [
    {
        "customer_id": 1,
        "first_name": "Bob",
        "last_name": "Lin",
        "last_update": "2022-11-14 04: 34: 58",
        "pincode": 382330,
        "isActive": 1
    },
    .
    .
    .
    {
        "customer_id": 5,
        "first_name": "Eve",
        "last_name": "Page",
        "last_update": "2022-11-12 01: 59: 33",
        "pincode": 382345,
        "isActive": 1
    }
]