I have a set of documents which includes a nested set of revisions of that same document. I need to execute a search query that returns only the matched set, including the parent where matched. Below is a reduced example of the data.
{
"title_number": "46345734",
"status": "applied",
"updated": "02/04/2022",
"rev_num": 1,
"revs": [
{
"title_number": "46345734",
"status": "unapplied",
"updated": "02/06/2022",
"rev_num": 2
}]
},
{
"title_number": "46345802",
"status": "unapplied",
"updated": "02/05/2022",
"rev_num": 1,
"revs": [
{
"title_number": "46345802",
"status": "applied",
"updated": "02/05/2022",
"rev_num": 2
},
{
"title_number": "46345802",
"status": "applied",
"updated": "02/06/2022",
"rev_num": 3
}]
}
In one query, I want to look for all documents with a status of "unapplied", only returning the nested documents that match the status and keeping or removing the parent if it also matched.
Below is an example of how I expected the query to look initially, and the expected result when run against the provided example data.
titles.find({
"$and": [{
"$or": [
{"status": "applied"},
{"revs.status": "applied"}
]
}]
})
Expected(Wanted) result
{
"title_number": "46345734",
"status": "applied",
"updated": "02/04/2022",
"rev_num": 1,
"revs": [],
},
{
"title_number": "46345802",
"status": "applied",
"updated": "02/05/2022",
"rev_num": 2
},
{
"title_number": "46345802",
"status": "applied",
"updated": "02/06/2022",
"rev_num": 3
}
In the first result, the parent status matched, but not the child, so only the parent was returned. In the second result, the parent didn't match, however, two of the nested documents did, so only they were returned.
After reading a ton of posts regarding aggregates, element matching, etc, I have been able to get bits and pieces, but it seems my case is either insanely obvious or rather unique.
Thanks in advance for any assistance.
use aggregate and first $filter revs with applied status and then $push applied docs to separate array and also the revs to another array and finally mix two array to one.
you can test it here mongodb playground
titles.aggregate([
{
"$addFields": {
"revs": {
$filter: {
input: "$revs",
as: "rev",
cond: {
$eq: [
"$$rev.status",
"applied"
]
}
}
}
}
},
{
$group: {
_id: null,
applied_doc: {
$push: {
$cond: [
{
$eq: [
"$status",
"applied"
]
},
{
_id: "_id",
title_number: "$title_number",
status: "$status",
updated: "$updated",
rev_num: "$rev_num",
revs: "$revs"
},
"$$REMOVE"
]
}
},
applied_sub_doc: {
$push: "$revs"
}
},
},
// flatten array in $applied_sub_doc prop
{
$set: {
applied_sub_doc: {
$reduce: {
input: "$applied_sub_doc",
initialValue: [],
in: {
$concatArrays: [
"$$value",
"$$this"
],
},
},
},
},
},
{
$project: {
mixedDocs: {
$concatArrays: [
"$applied_doc",
"$applied_sub_doc"
],
},
},
},
{
$unwind: "$mixedDocs",
},
{
$replaceWith: "$mixedDocs",
},
])