I'm using mongolite in R to read a mongo collection with the following structure:
[{_id: 0, date: 20221201, dailyAnswer:[
{question:a,score:1},
{question:b,score:3},
{question:c,score:2}
]},
{_id: 1, date: 20221201, dailyAnswer:[
{question:a,score:3},
{question:b,score:2},
{question:c,score:1}
]},
{_id: 0, date: 20221202, dailyAnswer:[
{question:a,score:2},
{question:b,score:2},
{question:c,score:3}
]},
{_id: 1, date: 20221202, dailyAnswer:[
{question:a,score:3},
{question:b,score:1},
{question:c,score:1}
]}]
For each document I'd like to extract each question score into a column, with the table structure:
_id | date | question_a_score | question_b_score | question_c_score
In MongoDB Compass I've written a query to extract them:
{
q_a_score: { $arrayElemAt: [ "$dailyAnswer.score",0]},
q_b_score: { $arrayElemAt: [ "$dailyAnswer.score",1]},
q_c_score: { $arrayElemAt: [ "$dailyAnswer.score",2]}
}
Which returns:
[{
_id: 0,
question_a_score:1,
question_b_score:3,
question_c_score:2},
...,
{
_id: 1,
question_a_score:3,
question_b_score:1,
question_c_score:1}
}]
However, I'm not sure whether to use the $aggregate
or $find
methods in mongolite in R, and how to structure the pipeline or query arguments in those methods respectively.
1条答案
按热度按时间qv7cva1a1#
Use the
aggregate
method with the$project
and$arrayElemAt
operators: