lkurylo
(lkurylo)
1
I have following json structure:
I want to group it into this format (column VisitNo):
How can I do this using aggregation framework? Can’t find any similar example. I thought maybe to use the $accumulator but don’t know how to do it.
I want an equivalent of sql window function:
select u.RootId, u.VisitDate, count(u.VisitDate) over(partition by u.rootid order by u.visitdate) as VisitNo
From dbo.ProspectiveFollowUp u
lkurylo
(lkurylo)
2
I finally got it to work. But is there any othere, maybe better solution? Without the index usage?
db.dbo_ObservationJSON.aggregate([
{ $unwind: '$values' },
{ $unwind: { path: '$values.ProspectiveFollowUp', "includeArrayIndex": "index" } },
{
$project: {
_id: 0,
// Id: '$values.ProspectiveFollowUp.Id',
RootId: '$values.ProspectiveFollowUp.RootId',
VisitDate: '$values.ProspectiveFollowUp.VisitDate',
VisitNo: { $add: ['$index', 1] }
}
},
{
$sort: {
RootId: 1
}
}
])