Getting specified data with mongodb aggregation framework

i tried to convert into mongodb aggregation famework and every thing runs as expected

[
  {
    $match: {
      $and: [
        { $or: [{ make: null }, { make: "BMW" }] },
        { $or: [{ model: null }, { model: "320" }] },
        { $or: [{ price: null }, { price: { $gte: 1000 } }] },
        { $or: [{ price: null }, { price: { $lte: 80000 } }] },
      ],
    },
  },
  { $project: { _id: 0, make: 1, model: 1, price: 1 } },
];

but when not matching a “make” it returns nothing

[
  {
    $match: {
      $and: [
        { $or: [{ make: null }, { make: "asds" }] },
        { $or: [{ model: null }, { model: "320" }] },
        { $or: [{ price: null }, { price: { $gte: 1000 } }] },
        { $or: [{ price: null }, { price: { $lte: 80000 } }] },
      ],
    },
  },
  { $project: { _id: 0, make: 1, model: 1, price: 1 } },
];

i want to filtter cars by make, model, price
and when not match make, go to search for rest of query model, price
what should i do, or any other idea to make this filter

Hi @Mustafa_Wael,

Welcome to MongoDB community!

I am not sure I understand your end goal here.

Since the “make” field conditions are part of AND once your documents does have documents with field “make” populated with null values or with “asds” values the query is expected to give no results.

Please note that make : null is different to make : { $exists : false} so if your docs actually don’t have this field this will yield results.

Best
Pavel

The end goal is
If the “make” field not match the condition go to the “model” field if not go to “mode” field

Like what this sqlite query do

"
FROM car
    WHERE (@make is NULL OR @make = make)
    AND (@model is NULL OR @model = model)
    AND (@minPrice is NULL OR @minPrice <= price)
    AND (@maxPrice is NULL OR @maxPrice >= price)
"

i have this car collection

[
 {make: 'Audi', model: 'A2'},
 {make: 'BMW', model: '116'},
 {make: 'BMW', model: 'X1'},
 {make: 'BMW', model: '320'},
 {make: 'Ford', model: 'Fiesta'},
 {make: 'Mazda', model: '6'},
 {make: 'Merces-Benz', model: '200'},
 {make: 'Mazda', model: '6'},
 {make: 'Mazda', model: 'e250'},
]

and this aggregation that works as expected

[
  {
    $match: {
      $and: [
        { $or: [{ make: null }, { make: "BMW" }] },
        { $or: [{ model: null }, { model: "320" }] },
        { $or: [{ price: null }, { price: { $gte: 1000 } }] },
        { $or: [{ price: null }, { price: { $lte: 80000 } }] },
      ],
    },
  },
  { $project: { _id: 0, make: 1, model: 1, price: 1 } },
];

this aggregation will return this document
{ "make" : "BMW", "model" : "320", "price" : 18999 }

but when i use the below aggregation returns nothing

[
  {
    $match: {
      $and: [
        { $or: [{ make: null }, { make: "asds" }] },
        { $or: [{ model: null }, { model: "320" }] },
        { $or: [{ price: null }, { price: { $gte: 1000 } }] },
        { $or: [{ price: null }, { price: { $lte: 80000 } }] },
      ],
    },
  },
  { $project: { _id: 0, make: 1, model: 1, price: 1 } },
];

i know that this aggregation should return nothing because “make” field conditions are part of AND

so i want the correct aggregation to return this document => { "make" : "BMW", "model" : "320", "price" : 18999 }

the main idea is if “make” field is not match the condition will go to the next condition and if it not match will go to the next condition and so as

thanks for reply :blush:

Hi @Mustafa_Wael,

What you describe is an $or condition and not $and which has a short circuit mechanism, means first expression false everything is false.

However, it sounds that you should implement this search in stages , meaning query for “make” if no results query for model etc.

Index each field to speed up search.

Best
Pavel

1 Like

solved this by replace {field: null} to {field: {$exists: <false> || <true> } }

$match: {
      $and: [
        { $or: [{ make: { $exists: false } }, { make: "BMW" }] },
        { $or: [{ model: { $exists: true } }, { model: "asd" }] },
        { $or: [{ price: { $exists: false } }, { price: { $gte: 1000 } }] },
        { $or: [{ price: { $exists: false } }, { price: { $lte: 50000 } }] },
      ],
    },

will check if “make” field is exist or not.

if not exist ($exists: false) will continue to the next condition that is in “$or” operator and if is true will continue to the next “$or” condition in ‘$and’ operator,

if exist ($exists: true) will only continue to the next “$or” condition in ‘$and’ operator and so on.

Thanks for helping

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