I've got about 50k documents that have many fields, but the important ones for this issue look like this:
"_id": ObjectId(""),
...
"item": "apple"
"properties" : [
{
"color": "red",
"size" : "L",
"status": "eatable"
}
],
...
What I'm trying to print are lines that look like item+";"+properties.color, while retaining the integrity of the data I have, and printing these fields only for certain documents. For now the only thing I've had success with is printing either only the item or the whole array "properties", but I can't get neither the two of them at the same time nor just the color.
db.getCollection("foods").find({properties:{$elemMatch:{color: {$in:["red", "green", "orange", "blue"]}}}})
.aggregate([
{ $unwind: {path:"$properties", preserveNullAndEmptyArrays: true}},
]).forEach(function(row){
if(row.properties !== undefined) print (row.item + ";" + row.properties.color) })
This gives an error, because of the find before the aggregate, but I don't really know how else to make it so that it only prints this for the rows that fit that condition (and I also don't really know if the $unwind would mess up the arrays in my data, or if it is just a temporary "separation" while the function is running).
What I've got running right now, getting just the items:
db.getCollection("foods").find({properties:{$elemMatch:{color: {$in:["red", "green", "orange", "blue"]}}}})
.forEach(
function(row){
print (row.item)
}
)
By changing row.item to row.properties I got the full arrays printed, but adding .color after that did nothing.
Not sure if I'm understood but what about to prepare data into the query like this:
db.getCollection("foods").aggregate([
{
$match: {
"properties.color": {
$in: [
"red",
"green",
"orange",
"blue"
]
}
}
},
{
$project: {
_id: 0
item: 1,
color: {
"$arrayElemAt": [
"$properties.color",
0
]
}
}
}
])
Example here.
And getting something like this JSON below you can iterate easily and access to color
and item
property.:
[
{
"color": "red",
"item": "apple"
}
]