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

CodePudding user response：

Use $setUnion to distinct the value in the array.

{
  $set: {
    color: {
      $setUnion: "$table.color"
    },
    country: {
      $setUnion: "$table.country"
    },
    population: {
      $setUnion: "$table.population"
    }
  }
}

Demo @ Mongo Playground

CodePudding user response：

The answer from @Yong Shun is correct, but you can make some cosmetic improvements.

{
  $match: {
    "$and": [
      {
        "level_1": "A"
      },
      {
        "arno": "CD"
      }
    ]
  }
}

Can be written as

{ $match: { level_1: "A", arno: "CD" } }

and this

{
  "$or": [
    {
      "$eq": [
        "$$t.color",
        "blue"
      ]
    },
    {
      "$eq": [
        "$$t.color",
        "red"
      ]
    }
  ]
}

Can be written as

{$in: ["$$t.color", ["red","blue"]]}

and this

{
  "$or": [
    {
      "$regexMatch": {
        "input": "$$t.Supplier Manager",
        "regex": "Jim Brown",
        "options": "i"
      }
    },
    {
      "$regexMatch": {
        "input": "$$t.Supplier Manager",
        "regex": "Arnold Khan",
        "options": "i"
      }
    }        
  ]
}

Can be written as

{
  "$regexMatch": {
    "input": "$$t.Supplier Manager",
    "regex": "(Jim Brown)|(Arnold Khan)",
    "options": "i"
  }
}