Sorting on new fields created dynamically by an $addFields step in an aggregation pipeline

Working with Data
, ,
1

Hello,

I’m trying to understand how to improve an inside sorting operation for an aggregation, made on new fields created by an $addFields step. I’ve got a very articulated pipeline, which I’ll just show the part that I’m interested in:

[
... other steps ...
{ '$addFields': { 'list.a_new_field': { ... } },
{ '$addFields': { 'list.other_new_field': { '$sum': [ { '$max': '$list.a_new_field } ] } } },
{ '$sort': { 'list.other_new_field': -1 } },
... other steps ...
]

The sort is taking 60s to compute, as explain’d:

{ '$sort': { sortKey: { 'list.other_new_field': -1 } },
       nReturned: 3053,
       executionTimeMillisEstimate: 60667 } ]

The collection has 464 documents.
The problem here is that I don’t really know how to index the sorting, cause it’s on a new field. Is there any way I can optimize the query without messing with the logic of the pipeline?

3

Hi :wave: @Marco_D_Agostino,

Welcome to the MongoDB Community forums :sparkles:

An index cannot be created on a field that is generated within a pipeline, only on a field that resides in a collection.

I am curious, though, about what explain() return if you remove the $sort stage. Is it only the presence $sort stage that slows down the query? 60 seconds seems excessive to sort 3000 documents.

Alternatively, if speed is your main concern, you may be able to use the $merge / $out aggregation stage to create a materialized view and then create an index on the resulting collection. Note that you will have to periodically update the materialized view collection in order to get the most recent data.

I hope it helps!

Thanks,
Kushagra

4

Hello Kushagra,

thanks a lot for your reply. I thought just like you did, with $merge, but it’s a bit complicated cause the pipeline has other articulated steps and other sorts to do. I’ll think of a way to combine it with $merge.
Thank you very much!

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.