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
    }
]

---