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