My data has the following format:
[
{
"level_1": "A",
"cols": [
"A",
"B"
],
"arno": "DC",
"table": [
{
country: "NO",
population: 400,
color: "red"
},
{
country: "AE",
population: 100,
color: "red"
},
{
country: "OT",
population: 200,
color: "blue"
},
{
country: "AU",
population: 200,
color: "red",
alo: "n"
},
]
},
{
"level_1": "A",
"cols": [
"A",
"B"
],
"arno": "CD",
"table": [
{
country: "NO",
population: 200,
color: "blue",
"Supplier Manager": "['Arnold Khan']"
},
{
country: "AE",
population: 200,
color: "red",
"Supplier Manager": "[]"
},
{
country: "AE",
population: 200,
color: "green",
"Supplier Manager": "['Arnold Khan']"
},
{
country: "OT",
population: 200,
color: "blue",
"Supplier Manager": "['Adam Nor', 'Jim Brown']"
},
]
},
{
"level_1": "B",
"cols": [
"A",
"B"
],
"arno": "CD",
"table": [
{
country: "AL",
population: 400,
color: "red",
alo: "y"
},
{
country: "AR",
population: 100,
color: "green",
alo: "y"
},
{
country: "YU",
population: 200,
color: "red",
alo: "y"
},
{
country: "AX",
population: 200,
color: "red",
alo: "n"
},
]
}
]
I am running the following query to retrieve the values from nested array table inside of all objects in my database:
db.collection.aggregate([
{
$match: {
"$and": [
{
"level_1": "A"
},
{
"arno": "CD"
}
]
}
},
{
"$addFields": {
"table": {
"$filter": {
"input": "$table",
"as": "t",
"cond": {
"$and": [
{
"$or": [
{
"$eq": [
"$$t.color",
"blue"
]
},
{
"$eq": [
"$$t.color",
"red"
]
}
]
},
{
"$eq": [
"$$t.population",
200
]
},
{
"$or": [
{
"$regexMatch": {
"input": "$$t.Supplier Manager",
"regex": "Jim Brown",
"options": "i"
}
},
{
"$regexMatch": {
"input": "$$t.Supplier Manager",
"regex": "Arnold Khan",
"options": "i"
}
},
]
}
]
}
}
}
}
}
])
Now the result I get is this:
[
{
"_id": ObjectId("5a934e000102030405000001"),
"arno": "CD",
"cols": [
"A",
"B"
],
"level_1": "A",
"table": [
{
"Supplier Manager": "['Arnold Khan']",
"color": "blue",
"country": "NO",
"population": 200
},
{
"Supplier Manager": "['Adam Nor', 'Jim Brown']",
"color": "blue",
"country": "OT",
"population": 200
}
]
}
]
Which is correct, but I want now to get list distinct values for each variable in the table array of objects from aggregating into this result for the table array output.
Like for example get distinct:
'color' : ["blue"]
'country' : ["OT", "NO"]
'population' : [200]
...
Is this achievable in Mongodb aggregate query or is it better to load this into Pandas DataFrame and retrieve it from there?
Working playground example: https://mongoplayground.net/p/VSOZ2YVQa4w
Use $setUnion to distinct the value in the array.
{
$set: {
color: {
$setUnion: "$table.color"
},
country: {
$setUnion: "$table.country"
},
population: {
$setUnion: "$table.population"
}
}
}