How can i sort data coming from multiple tables joined by lookup

[Naila_Nosheen]

0

In my MongoDB, I have these documents

1. Topic, 2. MCQS, 3. True/false The topic has different questions like MCQs and true/false and in the MCQs and true/false table the Id of the topic is saved as a foreign key and in the topic table, I have userId saved as a foreign key so that I can get the topics created by a specific user. Now I want to make an API that can get data from these two documents(MCQs, t/f) on the basis of topicId. Can anyone please help me, I am creating the backend for the first time. I joined these table by using lookup.

Topic.aggregate([

        { $match: { _id: new ObjectId(req.params.id) } },

        {

            $lookup: {

                from: "mcqs",

                localField: "_id",

                foreignField: "topicId",

                as: "topics_mcqs_info"

            },

        },

        {

            $lookup: {

                from: "true_falses",

                localField: "_id",

                foreignField: "topicId",

                as: "topics_trueFalse_info"

            }

        },
    ])

I am getting the result now I want to sort the questions by their sequence number.
This my question json object. How can I sort questions? As I am getting first all the mcqs. then true false.

{
                "_id": "629f451869b9778bdd7f4b16",
                "mcqs": "Hello mcqs",
                "option1": "a",
                "option2": "b",
                "option3": "c",
                "option4": "d",
                "answer": "option2",
                "sequence": 1,
                "topicId": "629f44f969b9778bdd7f4b10"
            }

[MaBeuLux88 (Maxime Beugnet)]

Hi @Naila_Nosheen and welcome in the MongoDB Community !

You have the wrong way to do it and the right way to do it.
The wrong way would be to use $unwind to break down the array (I think topics_mcqs_info here) and rebuild the question array with a $group using $push and with a $sort before that group stage to sort the docs in the order you want.

This would work but add a lot of useless processing (=breaking down the array and rebuilding it).

The right solution is to use the other format of $lookup, the one that is using a subpipeline, so you can actually sort the docs directly in there and build the array of questions already sorted.

You pipeline will probably look like this:

db.orders.aggregate( [
   {
      $lookup: {
         from: "mcqs",
         localField: "_id",
         foreignField: "topicId",
         pipeline: [ {
            $sort: {
               sequenceNumber: 1
            }
         } ],
         as: "topics_mcqs_info"
      }
   }, 
   { the other similar lookup stage here }
] )

I think this should work. Please provide a few sample docs if you can’t figure it out so I can test it on my side.

Cheers,
Maxime.

[Naila_Nosheen]

Hello @MaBeuLux88 I have tried the pipeline way. But the questions are not sorted. this JSON object I am getting.

[
    {
        "_id": "62a4769c989f1ace846eaf35",
        "topic": "Plants",
        "language": "English",
        "grade": "ELEMENTARY SCHOOL Grade 1",
        "noOfQuestions": "3",
        "__v": 0,
        "topics_mcqs_info": [
            {
                "_id": "62a476f7989f1ace846eaf44",
                "mcqs": "I am MCQs.",
                "option1": "option1",
                "option2": "option2",
                "option3": "option3",
                "option4": "option4",
                "answer": "option2",
                "sequence": 3,
                "topicId": "62a4769c989f1ace846eaf35",
                "__v": 0
            }
        ],
        "topics_trueFalse_info": [
            {
                "_id": "62a476b9989f1ace846eaf3b",
                "question": "I am true false.",
                "answer": "true",
                "sequence": 1,
                "topicId": "62a4769c989f1ace846eaf35",
                "__v": 0
            }
        ],
        "topics_openEnded_info": [
            {
                "_id": "62a476cb989f1ace846eaf3f",
                "question": "I am short question.",
                "sequence": 2,
                "topicId": "62a4769c989f1ace846eaf35",
                "__v": 0
            }
        ]
    }
]

As In my code the mcqs lookup is first so it is returning me mcqs first regardless of the sequence.

This is my code:

const getQuestionsByTopicId = function (req, res) {
    Topic.aggregate([
        { $match: { _id: new ObjectId(req.params.id) } },
        {
            $lookup: {
                from: "mcqs",
                localField: "_id",
                foreignField: "topicId",
                pipeline: [ {
                    $sort: {
                        sequence: 1
                    }
                 } ],
                as: "topics_mcqs_info"
            },
        },
        {
            $lookup: {
                from: "true_falses",
                localField: "_id",
                foreignField: "topicId",
                pipeline: [ {
                    $sort: {
                        sequence: 1
                    }
                 } ],
                as: "topics_trueFalse_info"
            }
        },
        {
            $lookup: {
                from: "open_endeds",
                localField: "_id",
                foreignField: "topicId",
                pipeline: [ {
                    $sort: {
                        sequence: 1
                    }
                 } ],
                as: "topics_openEnded_info"
            }
        }
    ])
        .then((result) => {
            res.status(200).send(result)
            console.log(result);
        })
        .catch((error) => {
            console.log(error);
        });
}

Can you please see this for me? As I am noticing it has sorted the array in lookups, but is there a way to sort it that way to get the output I want?

[MaBeuLux88 (Maxime Beugnet)]

Hi @Naila_Nosheen,

I don’t understand what isn’t sorted in your output. Each lookup adds a new array of documents that come from a different collection each time and these arrays are sorted by sequence in each individual array. It’s not what you want?

Do you want to mix all these arrays in a single array where all the questions are mixed and sorted by sequence?

Also please use markdown code blocks when you send code to ease the readability of your posts.

[Naila_Nosheen]

Sorry for the late reply but thank you so much. Your Solution helped me a lot. but is it possible to mix all the questions in one array and then sort them by sequence?

[MaBeuLux88 (Maxime Beugnet)]

Hi @Naila_Nosheen,

Then I would remove the sorts from the lookups (as they would be redundant) and I would add the following at the end of your pipeline:

[
  {
    '$addFields': {
      'all': {
        '$sortArray': {
          'input': {
            '$concatArrays': [
              '$topics_mcqs_info', '$topics_trueFalse_info', '$topics_openEnded_info'
            ]
          }, 
          'sortBy': {
            'sequence': 1
          }
        }
      }
    }
  }, {
    '$project': {
      'topics_mcqs_info': 0, 
      'topics_trueFalse_info': 0, 
      'topics_openEnded_info': 0
    }
  }
]

Note that $sortArray is new in 5.2.

If 5.2 isn’t yet possible for you, then you have an alternative but it’s a little bit unpleasant. Read this for more details.

Cheers,
Maxime.

[Naila_Nosheen]

Thank you so much @MaBeuLux88. You saved my day.