How to do join in restheart

[prakashsubramaniam]

Hi Team,

I want to refer two collection and need to retrieve the data from mongo db and I have created the aggregation meta data in mongo and passing the avars variable also. But unforutnately I keep on getting an error as "Query does not exist". I have provided the sample which I tried for aggregation first as per the issue101 below,

Aggregation MetaData:

{
    "_id" : ObjectId("5af121a8"),
    "aggrs" : [ 
        {
            "type" : "pipeline",
            "uri" : "totalitem",
            "stages" : [ 
                {
                    "_$match" : {
                        "Item" : {
                            "_$var" : "Item"
                        }
                    }
                }, 
                {
                    "_$group" : {
                        "_id" : "$Item",
                        "total" : {
                            "_$sum" : "$Amount"
                        }
                    }
                }
            ]
        }
    ]
}

URLs I used:

https://10.200.1.168:4443/cache/A/_aggrs/totalitem?avars={'Item':'a'}

https://10.200.1.168:4443/cache/A/_aggrs/totalitem?'avars={"Item":"a"}'

https://10.200.1.168:4443/cache/A/_aggrs/totalitem?avars='{"Item":"a"}'

https://10.200.1.168:4443/cache/A/_aggrs/totalitem?avars={"Item":"a"}

Error Message for all the above url:

{"http status code":404,"http status description":"Not Found","message":"query does not exist"}

Mongo DB version = 3.6 Restheart Version: 3.1.1

Please let me know if you need more information regarding the issue.

[prakashsubramaniam]

Hi Team,

Below is the exact scenario what I want to perform in restheart,

employment collection data:

{
    "_id" : ObjectId("5af12e703"),
    "employeeId" : "P876",
    "employeeName" : "Mark",
    "employeeSurname" : "Smith",
    "gender" : "Male",
    "age" : "42",
    "employmentType" : "Permanent"
}

{
    "_id" : ObjectId("5af12e"),
    "employeeId" : "P878",
    "employeeName" : "Andrew",
    "employeeSurname" : "Ken",
    "gender" : "Male",
    "age" : "38",
    "employmentType" : "Permanent"
}

designation collection data:

{
    "_id" : ObjectId("5af12eaa"),
    "id" : "P876",
    "designation" : "TechnicalArchitect",
    "department" : "ApplicationDevelopment",
    "level" : "A5"
}

{
    "_id" : ObjectId("5af12"),
    "id" : "P878",
    "designation" : "DesignArchitect",
    "department" : "ApplicationDevelopment",
    "level" : "A4"
}

aggregation Meta Data in Employment Collection:

 { "aggrs" : [ 
        {
            "stages" : [ 
                {
                    "_$lookup" : {
                        "from" : "designation",
                        "localField" : "employeeId",
                        "foreignField" : "id",
                        "as" : "employeeInfo"
                    }
                }
            ],
            "type" : "pipeline",
            "uri" : "employeeDetails"
        }
    ]
}

restheart URL:https://00.000.0.000:4443/cache/employment/_aggrs/employeeDetails

Error Message:

{"http status code":404,"http status description":"Not Found","message":"query does not exist"}

ExpectedOutput:

{  
   "employeeInfo":{  
      "employeeId":"P876",
      "employeeName":"Mark",
      "employeeSurname":"Smith",
      "gender":"Male",
      "age":"42",
      "employmentType":"Permanent",
      "designation":"TechnicalArchitect",
      "department":"ApplicationDevelopment",
      "level":"A5"
   }
}

Could you please help me to achieve this?

[ujibang]

do you have anything in the restheart log when you GET /cache/employment/_aggrs/employeeDetails

[prakashsubramaniam]

Hi Ujibang,

Sorry, I did not check the restheart log as it is in development server and also could you please let me know what is the path for error log?

[ujibang]

logging is configured by this section of the conf file

if you didn't touch it, it should be just in the console output or restheart.log in system temporary directory if you enabled enable-log-file

#### Logging

# enable-log-console: true => log messages to the console (default value: true)
# enable-log-file: true => log messages to a file (default value: true)
# log-file-path: to specify the log file path (default value: restheart.log in system temporary directory)
# log-level: to set the log level. Value can be OFF, ERROR, WARN, INFO, DEBUG, TRACE and ALL. (default value is INFO)
# requests-log-level: log the request-response. 0 => no log, 1 => light log, 2 => detailed dump
# WARNING: use requests-log-level level 2 only for development purposes, it logs user credentials (Authorization and Auth-Token headers)

enable-log-file: false
#log-file-path: /tmp/restheart.log
enable-log-console: true
log-level: INFO
requests-log-level: 1

[prakashsubramaniam]

Hi Ujibang,

I am not able to see any error message in restheart.log file, other than the below details,

[XNIO-1 task-2] INFO  o.r.handlers.RequestLoggerHandler - GET https://00.000.0.000:4443/cache/employment/_aggrs/employeeDetails from /000.000.00.0:7094 => status=404 elapsed=5ms contentLength=95 username=restheart roles=[users]

[ujibang]

I just tried as follows with httpie

$ http -a a:a PUT 127.0.0.1:8080/db/issue280 aggrs:='[{"stages":[{"_$lookup":{"from":"designation","localField":"employeeId","foreignField":"id","as":"employeeInfo"}}],"type":"pipeline","uri":"employeeDetails"}]'

HTTP/1.1 201 Created

$ http -a a:a 127.0.0.1:8080/db/issue280
HTTP/1.1 200 OK
...

{
    "_embedded": [],
    "_etag": {
        "$oid": "5af19e81b4229b3573d46bca"
    },
    "_id": "issue280",
    "_links": {
        "employeeDetails": {
            "href": "/db/issue280/_aggrs/employeeDetails"
        }
    },
    "_returned": 0,
    "aggrs": [
        {
            "stages": [
                {
                    "_$lookup": {
                        "as": "employeeInfo",
                        "foreignField": "id",
                        "from": "designation",
                        "localField": "employeeId"
                    }
                }
            ],
            "type": "pipeline",
            "uri": "employeeDetails"
        }
    ]
}

http -a a:a 127.0.0.1:8080/db/issue280/_aggrs/employeeDetails
HTTP/1.1 200 OK
...

{
    "_embedded": [],
    "_returned": 0,
    "_size": 0,
    "_total_pages": 0
}

the aggregation runs without problems.

[prakashsubramaniam]

Hi Ujibang,

Thank you for your response!

Yes it works fine now, There was a problem with my code and it returns the data from DB.But Unforunately it is returning all the documents what ever present in Source Collection and it is not performing $lookup with the target collection, when I do aggregate directly in Mongo with the same aggregation meta data it works perfectly. It fails to return the expected output only through rest heart.

Below is the aggregation meta data and I would request you to validate and let me know if there is any issue in it.

MetaData:

{
    "_id": ObjectId("5af1cabb"),
    "aggrs": [{
        "stages": [{
                "_$lookup": {
                    "from": "designation",
                    "localField": "employeeId",
                    "foreignField": "id",
                    "as": "employeeInfo"
                }
            },
            {
                "_$unwind": "$employeeInfo"
            }
        ],
        "type": "pipeline",
        "uri": "employeeDetails"
    }]
}

[prakashsubramaniam]

Hi Ujibang,

Could you please validate the meta data once and let me know if there is any issue with either metadata or MongoDB/Restheart version?

Thanks!