how do i convert sqlite query into mongodb query?

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

show mongodb $exists operator in details

so i have this ‘car’ collection

[
 {make: 'Audi', model: 'A2', price: 13999},
 {make: 'BMW', model: '116', price: 12999},
 {make: 'BMW', model: 'X1', price: 18999},
 {make: 'BMW', model: '320', price: 24000},
 {make: 'BMW', model: '320', price: 50999},
 {make: 'Ford', model: 'Fiesta', price: 14999},
 {make: 'Mazda', model: '6', price: 13999},
 {make: 'Merces-Benz', model: '200', price: 38999},
 {make: 'Mazda', model: '6', price: 16999},
 {make: 'Mazda', model: 'e250', price: 11999},
]

and if i search about

  1. make = ‘BMW’
  2. model = ‘asd’ (value is not provided in our db collection)
  3. minPrice = 1000
  4. maxPrice = 40000

will return this result

 {make: 'BMW', model: '116', price: 12999},
 {make: 'BMW', model: 'X1', price: 18999},
 {make: 'BMW', model: '320', price: 24000},

without this document that has ‘price = 50999’

{make: 'BMW', model: '320', price: 50999}

that is mean “price” condition is working as well

let’s code this aggregation

i have a pipeline that have two stages ‘$match’ and ‘$project’

const pipeline = [
  {
    $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 } }] },
      ],
    },
  },
  { $project: { _id: 0, make: 1, model: 1, price: 1 } },
];

in this “$or” condition

{ $or: [{ make: { $exists: false } }, { make: "BMW" }] }

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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top