Title: Will aggregation pipeline use index for derived field?
Tags: mongodb;mongodb-query
Question: In aggregation pipeline I have this:-
"$project" =&gt; {
account_name_i: { "$toLower" =&gt; "$account_name" },
"$sort" =&gt; {
account_name_i: 1
and I have index { account_name: 1 }
My Question is will $sort use index on account_name? If no, there is any other way we can achieve this in aggregation pipeline?
Comment: Since, the changed value is function of original value and the output will always be time-invariant (unchanged with time). So, I just thought if it is possible in Mongo

Comment: No, it will not, how can it if you have changed the value? To answer the latter: no as well and I know nof no way around this. You are wanting: https://jira.mongodb.org/browse/SERVER-90

Comment: No, it does not matter. An index can only be used on exact matcing of previous value. Once you change the value to even a different case which changes the unicode values then the index is useless


Here is another answer: If you are using Aggregation Pipeline Stages like $group or $match on a collection then it will
produce a new set of documents from that collection.
as it is new set of documents which means no index is defined for it and hence we cant use the index in Aggregation Pipeline
Note: although if you use $sort as first stage of Aggregation Pipeline then it will utilize the index of
the collection as input for this stage is collection itself.
Comment for this answer: Since he wishes to sort case insensitive this is not possible since indexes in MongoDB are not case insensitive

Here is another answer: No. The aggregation pipeline can only use a standard index on a $match or $sort phase that's at the beginning of a pipeline. The rules for using indexes with aggregation pipelines are described in detail in the manual.
You aim to sort on the lower-case version of account_name_i, most likely to function as a case-insensitive sort. To achieve this with an index, store a lower-case-normalized version of account_name_i in each document
{ "account_name_i" : "TruMAn's HABerdaShEry", "account_name_i_lc" : "truman's haberdashery" }
and index the normalized field ({ "account_name_i_lc" : 1 }).
Comment for this answer: This is currently the only method though I say that with huge regret that case insensitive indexes still have not been implemented.

Here is another answer: Kind of, though only with preprocessing. You can do an output after your project stage and query the output collection for sorting:
db.yourSource.aggregate([
"$project" =&gt; {
account_name_i: { "$toLower" =&gt; "$account_name" },
$out: "intermediate"
db.intermediate.ensureIndex({account_name_i:1})
db.intermediate.find({}).sort({account_name_i:1})

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:11/27/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mongodb><mongodb-query>', 'Title': 'Will aggregation pipeline use index for derived field?', 'ViewCount': 83, 'id': 27167464, 'user_AboutMe': '<p>Know more about me on Upwork <a href="https://www.upwork.com/o/profiles/users/%7E01f65577d1c44be459/" rel="nofollow noreferrer">https://www.upwork.com/o/profiles/users/~01f65577d1c44be459/</a></p>\n<p>Passionate and goal-oriented IT Leader with demonstrated experience of more than 10 years in software architecture, software development, and software consulting. I have served at a various technical positions in multinational organisations and have won several accolades for my work.</p>\n<p>My services and skills include:-</p>\n<ul>\n<li>Web Development with backends in Ruby on Rails or NodeJS ( using Express JS ).</li>\n<li>Development for projects involving frontends in ReactJS or Angular2 using Typescript.</li>\n<li>Development for projects involving simple vanilla javascript or jQuery.</li>\n<li>Creating RESTful APIs using Ruby on Rails/GraphQL/NodeJS</li>\n<li>Integration for third-party RESTful APIs with your existing/new system.</li>\n<li>App deployment on AWS infrastructure and Heroku.</li>\n<li>Development using a test-first approach using TDD or BDD.</li>\n</ul>\n', 'user_AccountId': 1573315.0, 'user_DisplayName': 'techvineet', 'user_Reputation': 4971.0, 'user_Views': 630.0, 'user_id': 1460496.0}}