Sort element with property: true to the top, but only one out of many

Working with Data
,
1

My app can search through a database of resources using MongoDB’s aggregation pipeline. Some of these documents have the property sponsored: true.

I want to move exactly one of these sponsored entries to the top of the search results, but keep natural ordering up for the remaining ones (no matter if sponsored or not).

Below is my code. My idea was to make use of addFields but change the logic so that it only applies to the first element that meets the condition. Is this possible?

[...]

const aggregationResult = await Resource.aggregate()
    .search({
        compound: {
            must: [
                [...]
            ],
            should: [
                [...]
            ]
        }
    })
    [...]
    //only do this for the first sponsored result
    .addFields({
        selectedForSponsoredSlot: { $cond: [{ $eq: ['$sponsored', true] }, true, false] }
            })
    .sort(
        {
            selectedForSponsoredSlot: -1,
            _id: 1
        }
    )
    .facet({
        results: [
            { $match: matchFilter },
            { $skip: (page - 1) * pageSize },
            { $limit: pageSize },
        ],
        totalResultCount: [
            { $match: matchFilter },
            { $group: { _id: null, count: { $sum: 1 } } }
        ],
        [...]
    })
    .exec();

[...]
3

Hi @Florian_Walther,

I want to move exactly one of these sponsored entries to the top of the search results, but keep natural ordering up for the remaining ones ( no matter if sponsored or not ).

From what it sounds like here, it doesn’t really sound like a sort per se. It sounds like you are wanting to “re-arrange” the result set. Is this correct?

In saying so, could you provide the following details so I can try work out a possible method to see if the desired output can be achieved?:

  1. MongoDB Version.
  2. Search index details.
  3. Full $search stage details
  4. 4-5 sample documents.
  5. The expected / desired output.
  6. Output from the $search stage whilst highlighting which document where sponsored:true you wish to have moved to the top (e.g. if there are multiple documents in the result set with sponsored:true , which one should go to the top? The highest scoring, or a random one)

Please redact any personal or sensitive information before posting it here

Regards,
Jason

3 Likes
5

Thank you for answering. Here is my aggregate with my current approach. It works, but the problem is that the reordering makes it show 11 results (instead of 10) on the first page (where the sponsored entry moved) and 9 results on the page where the sponsored entry would’ve usually been.

        const queryString = req.query.q;
        [...]
        const queryWords = queryString.split(" ");
        [...]
        const matchFilters: { $match: QueryOptions }[] = [{ $match: { approved: true } }];

        if (typeFilter) matchFilters.push({ $match: { type: { $in: typeFilter } } });
        if (fromYearFilter) {
            if (fromUnknownYearFilter === 'exclude') {
                matchFilters.push({ $match: { year: { $gte: fromYearFilter } } });
            } else {
                matchFilters.push({ $match: { $or: [{ year: { $gte: fromYearFilter } }, { year: '' }] } });
            }
        }
        if (languageFilter) matchFilters.push({ $match: { language: { $in: languageFilter } } });

        const SPONSORED_RESULT_SLOTS = 1;

        [...]

        let aggregation = Resource.aggregate()
            .search({
                compound: {
                    must: [{
                        wildcard: {
                            query: queryMainWordsWithSynonymsAndWildcards.flat(),
                            path: ['title', 'topics', 'link', 'creatorName'],
                            allowAnalyzedField: true,
                            score: { constant: { value: 1 } }
                        }
                    }
                    ],
                }
            })
            [...]
            .facet({
                sponsoredResult: [
                    ...matchFilters,
                    { $match: { sponsored: true } },
                    { $limit: SPONSORED_RESULT_SLOTS },
                    {
                        $lookup: {
                            from: "resourcevotes",
                            localField: "_id",
                            foreignField: "resourceId",
                            as: "votes",
                        }
                    }
                ],
                results: [
                    ...matchFilters,
                    { $skip: (page - 1) * pageSize },
                    { $limit: pageSize },
                    {
                        $lookup: {
                            from: "resourcevotes",
                            localField: "_id",
                            foreignField: "resourceId",
                            as: "votes",
                        }
                    }
                ],
                totalResultCount: [
                    ...matchFilters,
                    { $group: { _id: null, count: { $sum: 1 } } }
                ],
                [...]
            })
            .append({
                $set: {
                    results: {
                        $filter: {
                            input: "$results",
                            cond: { $not: { $in: ["$$this._id", "$sponsoredResult._id"] } }
                        }
                    }
                }
            });

        if (page === 1) {
            aggregation = aggregation
                .append({
                    $set: {
                        results: {
                            $concatArrays: ["$sponsoredResult", "$results"]
                        },
                    }
                });
        }

The document schema:


const resourceSchema = new mongoose.Schema<Resource>({
    title: { type: String },
    topics: [{ type: String, required: true }],
    description: { type: String },
    type: { type: String, required: true },
    year: { type: String },
    language: { type: String, required: true },
    sponsored: { type: Boolean },
});
6

I’m having trouble visualising this but is the structure of the page items for page 1 and page 2 somewhat like the below high level examples:

(I have just grouped up the page items for example purposes to help clarify)

Page 1 (11 documents):

1. "sponsored" : true /// The "one out of many" sponsored:true document you have taken to the top
2 - 11. "sponsored" : ... /// I presume the values here don't matter. Correct me if I am wrong here.

Page 2 (9 documents):

12 - 20. "`sponsored" : ...  /// I presume the values here don't matter. Correct me if I am wrong here.

I am wondering if the application logic could be altered to achieve what you are after. However, I would need more information for this. Can you provide the following information:

  1. MongoDB Version.
  2. Search index details.
  3. 4-5 sample documents.
  4. The expected / desired output.
  5. Output from the $search stage whilst highlighting which document where sponsored : true you wish to have moved to the top (e.g. if there are multiple documents in the result set with sponsored:true , which one should go to the top? The highest scoring, or a random one)
  6. Confirmation that the page structures is as I mentioned initially in this reply.

Regards,
Jason