I have a collection in MongoDB with many documents like the ones below:
[{
action: "SEND_ACTION_START",
data: {
stuff: {
delivered: {
a: 1,
b: 2,
c: 3
}
}
}
},
{
action: "SEND_ACTION",
data: {
stuff: {
delivered: [
{0: {
a: 1,
b: 2,
c: 3
}}
]
}
}
},
{
action: "SEND",
data: {
stuff: {
delivered: [
{0: {
a: 1,
b: 2,
c: 3
}},
{1: {
a: 11,
b: 22,
c: 33
}}
],
marker: 0
}
}
}]
Every case in the above array of documents has its own different structure: the first one is easy to query for fields, but having the other two makes the task difficult.
The second document contains an array of delivered with only a single element but the third case can contain many objects inside the array and that's the reason of the marker field which indicates which element on the array should be pulled out.
I would like to retrieve such documents in a single aggregation query (which will reduce the database stress as it is in a production environment), and get something like the document below:
{
a: 1,
b: 2,
c: 3
}
Can it be made in MongoDB from 4.4 version on?
CodePudding user response：
https://mongoplayground.net/p/kZSugkMmogf
Guess, the keys 0 and 1 are redundant.
db.collection.aggregate([
{
$match: {
action: "SEND",
}
},
{
$replaceRoot: {
newRoot: {
$arrayElemAt: [
"$data.stuff.delivered",
{
$toInt: "$data.stuff.marker"
}
]
}
}
}
])
[
{
"0": {
"a": 1,
"b": 2,
"c": 3
}
}
]
https://mongoplayground.net/p/6utBamY5-KW
I'm sure it can be simplified:
db.collection.aggregate([
{
$replaceRoot: {
newRoot: "$data.stuff"
}
},
{
$facet: {
a: [
{
$match: {
$expr: {
$eq: [
{
$type: "$delivered"
},
"object"
]
}
}
},
//
//
{
$replaceRoot: {
newRoot: "$delivered"
}
},
],
///
b: [
{
$match: {
$expr: {
$eq: [
{
$type: "$delivered"
},
"array"
]
}
},
},
//
{
$match: {
marker: {
"$exists": false
}
}
},
//
{
$project: {
delivered: {
$first: "$delivered"
}
}
},
//
{
$project: {
delivered: {
$objectToArray: "$delivered"
}
}
},
//
{
$replaceRoot: {
newRoot: {
$first: "$delivered.v"
}
}
},
],
//
c: [
{
$match: {
marker: {
"$exists": true
}
}
},
{
$replaceRoot: {
newRoot: {
$last: {
$objectToArray: {
$arrayElemAt: [
"$delivered",
{
$toInt: "$marker"
}
]
}
}
}
}
},
//
{
$replaceRoot: {
newRoot: "$v"
}
}
]
}
},
//
{
$project: {
result: {
$concatArrays: [
"$a",
"$b",
"$c"
]
}
}
},
])
[
{
"result": [
{
"a": 41,
"b": 42,
"c": 43
},
{
"a": 31,
"b": 32,
"c": 33
},
{
"a": 11,
"b": 22,
"c": 33
}
]
}
]