MongoDB Dedupe and Sort using Reduce

Question

I'm using Reduce to create a joined String of fields from an array.

For example, let's say I have an array of subdocuments called children - and each child has a name field.

e.g

[
   {name:"Zak"}, {name:"Bob"}, {name:"Sharon"}, {name:"Zak"}, {name:"Cindy"}, {name:"Bob"}, {name:"Peter"}
]

The expression below will give me a "csv" of "Zak, Bob, Sharon, Zak, Cindy, Bob, Peter, ";

(Yes - I know I can use $cond to detect the last iteration and remove the trailing ", ".

  uniqueCsv: {
   $reduce: {
     input: "$children",
     initialValue: '',
     in: {
       $concat: ["$$this.name", ", ", "$$value"]
     }
   }
  }

Is it possible to dedupe and sort with this reduce so that we can have

"Bob, Cindy, Peter, Sharon, Zak" ?

Thanks!

user_8987128 · Answer

$setUnion to get unique elements from children.name and this will sort string in ascending order
$concat to pass first parameter as $$value and second as condition if value is empty then return empty otherwise ", " and third as $$this means current string

db.collection.aggregate([
  {
    $project: {
      uniqueCsv: {
        $reduce: {
          input: { $setUnion: "$children.name" },
          initialValue: "",
          in: {
            $concat: [
              "$$value",
              { $cond: [{ $eq: ["$$value", ""] }, "", ", "] },
              "$$this"
            ]
          }
        }
      }
    }
  }
])

Playground

Second approach using $substr instead of $cond

$substr to pass current reduce operation, reduce operation will return , Bob, Cindy, Peter, Sharon, Zak
now we just need to remove first 2 character from above string, second parameter to start string from 2 second position and third parameter to pass length of string -1 will return all strings

db.collection.aggregate([
  {
    $project: {
      uniqueCsv: {
        $substr: [
          {
            $reduce: {
              input: { $setUnion: "$children.name" },
              initialValue: "",
              in: {
                $concat: ["$$value", ", ", "$$this"]
              }
            }
          },
          2,
          -1
        ]
      }
    }
  }
])

Playground

Warning:

As per MongoDB $setUnion: The order of the elements in the output array is unspecified.

but as per my experience it return array of string in ascending order, i have used it in my projects as well, i have tested it every way but it gives 100% ascending order.

It is your choice to use this in your project or not without mongodb confirmation.

MongoDB Dedupe and Sort using Reduce

1 Answers