process/query multiple subdocuments in mongodb with Java driver

Question

I have document like

{
"_id" : ObjectId("5bfdb66f0f77e33b90f53fad"),
"email" : "[email protected]",
"appointments" : [
    {
        "year" : "2018",
        "months" : "12",
        "day" : "31",
        "hour" : "10",
        "minute" : "30",
        "doctorsName" : "Doc 1",
        "hospitalName" : "Place 1",
        "sortOrder" : "201812311030"
    },
    {
        "year" : "2018",
        "months" : "09",
        "day" : "31",
        "hour" : "10",
        "minute" : "30",
        "doctorsName" : "Doc 1",
        "hospitalName" : "Place 1",
        "sortOrder" : "201809311030"
    },
    {
        "year" : "2018",
        "months" : "11",
        "day" : "30",
        "hour" : "05",
        "minute" : "30",
        "doctorsName" : "Doc 1",
        "hospitalName" : "Place 1",
        "sortOrder" : "201811300530"
    }
],
"medicalData" : [
    {
        "year" : "2018",
        "months" : "09",
        "day" : "31",
        "symptomsList" : [“headache”,“nausea”,"cough"],
        "weight" : "70",
        "bloodPressureSystolic" : "120",
        "bloodPressureDiastolic" : "80",
        "medications" : [“med 1”,“med 2”],
        "sortOrder" : "20180931"
    },
    {
        "year" : "2018",
        "months" : "10",
        "day" : "31",
        "symptomsList" : [“headache”,"cough","Vomiting","Body Pain"],
        "weight" : "70",
        "bloodPressureSystolic" : "120",
        "bloodPressureDiastolic" : "80",
        "medications" : [“med 1”,“med 2”,"med 3"],
        "sortOrder" : "20181031"
    }
]
}

I want to process both the subdocuments based on different parameters. With the help of Aggregator I tried. But couldn't succeed.

I want data with all the fileds and appointment data should be greater than certain sortorder(order1) and medical data equal to another sortorder(order2) value. only one medical data will be available for that particular sortorder. Right now I am writing two query, one for Appointment and another for Medical Data and merging them to make a complete JSON.

Type of output I want is :

{
   "email" : "[email protected]",
   “appointments”:
          [{
                “year”:2018,
                “month”:8,
                “day”:1,
                “hour”:7,
                “minutes”:6,
                “doctorName”:”Doc 1”,
                “hospitalName”:”Place 1”
            },
            {
                “year”:2018,
                “month”:10,
                “day”:15,
                “hour”:11,
                “minutes”:16,
                “doctorName”:”Doc 1”,
                “hospitalName”:”Place 1”
             }],
   “symptoms”: [“headache”,“nausea”],
   “bloodPressureSystolic”:120,
   “bloodPressureDiastolic”:80,
   “weight”: 58.5,
   “medications”:[“med 1”,“med 2”]
   }

Please ignore the mismatch in data in expected result.

Answer 1

Thanks to @Senthur Deva, This is how I implemented in Java

    DBObject filterAppointment = new BasicDBObject();
    filterAppointment.put("input", "$appointments");
    filterAppointment.put("as", "apnmt");
    filterAppointment.put("cond", new BasicDBObject("$gt", Arrays.<Object>asList("$$apnmt.sortOrder", sortOrder1)));

    BasicDBObject projectionAppointment = new BasicDBObject("$filter", filterAppointment);

    DBObject filterMedData = new BasicDBObject();
    filterMedData.put("input", "$medicalData");
    filterMedData.put("as", "med");
    filterMedData.put("cond", new BasicDBObject("$eq", Arrays.<Object>asList("$$med.sortOrder", sortOrder)));

    BasicDBObject projectionMedData = new BasicDBObject("$filter", filterMedData);

    AggregateIterable<Document> findIterable = usersCollection.aggregate(
            Arrays.asList(
                    new Document("$match", new Document("_id",new ObjectId(userId))),
                    new Document("$project",new Document("email",1)
                                .append("appointments", projectionAppointment)
                                .append("medicalData", projectionMedData)),
                    new Document("$addFields",new Document("medicalData",new Document("$arrayElemAt", Arrays.asList("$medicalData", 0)))),
                    new Document("$project",new Document("email",1)
                                .append("appointments", 1)
                                .append("year", "$medicalData.year")
                                .append("months", "$medicalData.months")
                                .append("day", "$medicalData.day")
                                .append("symptomsList", "$medicalData.symptomsList")
                                .append("weight", "$medicalData.weight")
                                .append("bloodPressureSystolic", "$medicalData.bloodPressureSystolic")
                                .append("bloodPressureDiastolic", "$medicalData.bloodPressureDiastolic")
                                .append("medications", "$medicalData.medications")
                                .append("sortOrder", "$medicalData.sortOrder"))

                )   
        );