Create a sort of "tabular" projection using MongoDB aggregation

Question

Let's say that I have the following documents in a one of the stages in mongodb aggregation:

{
    type: "A",
    value: "A",
    index: 1
},
{
    type: "A",
    value: "B",
    index: 0
},
{
    type: "B",
    value: "a",
    index: 4
},
{
    type: "B"
    value: "b",
    index: 2
},
{
    type: "B",
    value: "c",
    index: 5
}

And I would like to process them using available aggregate stages (using syntax from Mongo 4.0) into:

{
    type: "A",
    values: ["B", "A", null, null, null, null]
},
{
    type: "B",
    values: [null, null, "b", null, "a", "c"]
}

I was trying to use $project with $reduce but still I don't know how to set element at a specific index.

EDIT: The size of values array is not given up-front. In the sample case it's assumed to be 6 because the largest index is 5. So values in every output document has to be aligned to that size.

Can you explain how you arrived at the processed output? How did you get the six elements in the values array? — prasad_
– prasad_, Commented Apr 7, 2020 at 1:50

mickl · Accepted Answer · 2020-04-07 03:23:24Z

1

I'm assuming you want to sort by index and there should be null, null, "b", "a", "c" for type B. Then you can use below aggregation:

db.collection.aggregate([
    {
        $sort: { index: 1 }
    },
    {
        $group: {
            _id: null,
            type: { $addToSet: "$type" }
            docs: { $push: "$$ROOT" }
        }
    },
    {
        $unwind: "$type"
    },
    {
        $project: {
            _id: 0,
            type: 1,
            values: {
                $map: {
                    input: "$docs",
                    in: {
                        $cond: [ { $eq: [ "$type", "$$this.type" ] }, "$$this.value", null ]
                    }
                }
            }
        }
    }
])

Basically the $addToSet operator allows you to build an array of unique type values which can be used then for $filter.

Mongo Playground

answered Apr 7, 2020 at 3:23

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

newdeveloper Over a year ago

pls help if you can for related questions in mongoDB - stackoverflow.com/questions/61067856/…

Collectives™ on Stack Overflow

Create a sort of "tabular" projection using MongoDB aggregation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related