Create an array from fields that are not null

Question

I'm trying to optimize a query of form A.X = value OR B.Y.Z = value OR C.H = value in Mongodb. DB engine uses 3 different indices to perform a query like this and then join results. It's somewhat slow so I decided to put everything in one array and create a multikey index on it. So I created an index and this query to update entries and populate it:

{
  '$addFields': { 
    'AllowedEntries': [ '$A.X', '$B.Y.Z', '$C.H' ]
  } 
}

It worked like a charm but then I realized that some of values are nulls and I want nulls out of array. So I went to this query:

{
  '$addFields': { 
    'AllowedEntries': { 
      '$map': { 
        'input': [ '$A.X', '$B.Y.Z', '$C.H' ], 
        'as': 'item', 
        'in': { 
          'columns': { 
            '$filter': { 
              'input': '$$item', 
              'as': 'elt', 
              'cond': { '$ne': [ '$$elt', null ] } 
            } 
          }
        }
      } 
    } 
  } 
}

Which doesn't work because of input to $filter must be an array not binData error. How can I do it atomically? In theory I could execute this query and then pull nulls out but I'd really like to do it in one update.

The $map shouldn't be required, $filter should be able to do that. — Joe
– Joe, Commented Oct 6, 2020 at 11:04
Could you provide an example? I'm not sure I'm getting your idea — Alex Zhukovskiy
– Alex Zhukovskiy, Commented Oct 6, 2020 at 13:02
The $filter operator is for picking elements from an array based on a boolean conditional, which is exactly what you are trying to do. Check out the example at docs.mongodb.com/manual/reference/operator/aggregation/filter/… — Joe
– Joe, Commented Oct 6, 2020 at 16:38
Cool. Worked like a charm. Is it possible not to get only unique values? I tried $addToSet but failed. Also don't you want to post an answer? I'l mark it since it helped me. — Alex Zhukovskiy
– Alex Zhukovskiy, Commented Oct 6, 2020 at 17:48

Alex Zhukovskiy · Accepted Answer · 2020-10-07 12:52:33Z

1

I ended up with following solution, to put unique not-null items in a new field. Many thanks to @Joe for help

{
  '$addFields': { 
    'AllowedEntries': { 
      '$setUnion': [[], { 
        '$filter': { 
          'input': [  '$A.X', '$B.Y.Z', '$C.H' ], 
          'as': 'elt', 
          'cond': '$$elt' 
        } 
      }] 
    } 
  } 
}

note that $setUnion with empty array is used instead of $addToSet because the latter works with column names while the former works with any expression which is filter in our case.

edited Oct 7, 2020 at 12:52

answered Oct 6, 2020 at 17:57

Alex Zhukovskiy

10.1k12 gold badges87 silver badges159 bronze badges

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

Collectives™ on Stack Overflow

Create an array from fields that are not null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related