Question 115950 in Mongodb Question posted in
The official documentation can be found here.

Getting distinct values from aggregate result in MongoDB

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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"
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search