Translate sql query into mongodb query

Working with Data
1

I’m trying to grasp the mongodb concepts by translating some of our sql queries into mongo aggregation framework.

I have an sql code:

select dbo.VisitNo(u.id) as visitNo , o.id, o.PatientId, u.VisitDate
from dbo.Observation o 
join dbo.ProspectiveFollowUp u on u.rootid = o.Id
order by o.PatientId

The dbo.VisitNo is implemented as:

CREATE FUNCTION dbo.VisitNo(@Id int)
RETURNS INT
AS
BEGIN

    DECLARE @VisitDate date, @RootId int
    SELECT @VisitDate=VisitDate, @RootId=RootId FROM dbo.ProspectiveFollowUp WHERE Id=@Id

    RETURN (SELECT COUNT(1) FROM dbo.ProspectiveFollowUp WHERE RootId = @RootId AND VisitDate <= @VisitDate)
     
END

result:

My document in Mongo has following structure:

{
   "_id",
   "values":[
      {
         "Id",
         "PatientId",
         "ProspectiveFollowUp":[
            "Id",
            "RootId",
            "VisitDate"
         ]
      }
   ]
}

The values array has always one element, but that’s how the data was imported. ProspectiveFollowUp has at least one record.

Creating query for retrieving the data was rather easy:

db.dbo_ObservationJSON.aggregate([
    { $unwind: '$values' },
    {
        $project: {
            _id: 0,
            Id: '$values.Id',
            PatientId: '$values.PatientId',
            VisitDate: '$values.ProspectiveFollowUp.VisitDate'
        }
    },
    { $unwind: '$VisitDate' },
    { $sort: { PatientId: 1 } }
])

The harder part is the custom function itself. I can’t think outside od tsql world yet, so I have hard time getting this to work. I have translated the function into mongo the following way:

var id = 4

var result = db.dbo.ObservationJSON.aggregate([ 
{ $unwind: '$values' }, 
{ $unwind: '$values.ProspectiveFollowUp' }, 
{ $project: { Id: '$values.ProspectiveFollowUp.Id', RootId: '$values.ProspectiveFollowUp.RootId', VisitDate: '$values.ProspectiveFollowUp.VisitDate', _id:0 }}, 
{ $match: { Id: id }} 
]).toArray()[0]

var totalResult = db.dbo_ObservationJSON.aggregate([{
    $unwind: {
        path: '$values'
    }
}, {
    $unwind: {
        path: '$values.ProspectiveFollowUp'
    }
}, {
    $project: {
        Id: '$values.ProspectiveFollowUp.Id',
        RootId: '$values.ProspectiveFollowUp.RootId',
        VisitDate: '$values.ProspectiveFollowUp.VisitDate'
    }
}, {
    $match: {
        RootId: result.RootId,
        VisitDate: {
            $lte: result.VisitDate
        }
    }
},{$count: 'total'}]).toArray()[0]

But don’t know how to integrate it into the aggregation pipeline above. Can I write the entire sql query equivalent into one mongo aggregate expression?

2

I got it to work the following way:

db.dbo_ObservationJSON.aggregate([
  { $unwind: '$values' },
  { $unwind: { path: '$values.ProspectiveFollowUp', "includeArrayIndex": "index" } },
  {
    $project: {
      _id: 0,
      VisitNo: { $add: ['$index', 1] },
      RootId: '$values.ProspectiveFollowUp.RootId',
      PatientId: '$values.PatientId',
      VisitDate: '$values.ProspectiveFollowUp.VisitDate'
    }
  },
  {
    $sort: {
      PatientId: 1
    }
  }
]);

Can anybody suggest me, can to write it differently, without the index for example?