how to convert query to use regex instead of equal

Question

I'm using MongoDB and i have the following records:

{
    name: "a",
    status: [
        { age: 15, closed: true},
        { age: 38, closed: true},
        { age: 42, closed: false},
    ]
},
{
    name: "b",
    status: [
        { age: 29, closed: true},
        { age: 5, closed: false},
    ]
}

I want to check if the before last object in status has for example age = 29.
so for that i have this working query:

db.col.find({
    $expr: {
        $eq: [
            29,
            {
                $let: {
                    vars: { beforeLast: { $arrayElemAt: [ "$status", -2 ] } },
                    in: "$$beforeLast.age"
                }
            }
        ]
    }
})

but i want now to check for example if age contain a value like "2". i need to use regex expression. is there anyway to convert that query to use regex inside/instead $eq operator ?
PS: i don't want to use aggregation because i'm working with an old version.

Ashh · Accepted Answer · 2019-03-12 09:09:37Z

1

You can use $indexOfCP to find sub string inside an string character

db.collection.find({
  "$expr": {
    "$ne": [
      {
        "$indexOfCP": [
          { "$toLower": {
            "$let": {
              "vars": {
                "beforeLast": {
                  "$arrayElemAt": [
                    "$status",
                    -2
                  ]
                }
              },
              "in": "$$beforeLast.age"
            }
          }},
          "2"
        ]
      },
      -1
    ]
  }
})

answered Mar 12, 2019 at 9:09

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

5 Comments

Ayoub k Over a year ago

is there anyway to make it "insensitive case" ?

Ashh Over a year ago

How can you use case insensitive with the numerical fields?

Ayoub k Over a year ago

i want to use the same login with a string field

Ashh Over a year ago

I am not able to getting your point. Please ask a new question with some proper clarification.

Ayoub k Over a year ago

I m just asking if there any possibility to use "/a/i" instead of "2".

Ashwanth Madhav · Accepted Answer · 2019-03-12 09:36:23Z

0

Try with aggregation

db.collection.aggregate([
{
 $project:
  {
     last: { $arrayElemAt: [ "$status", -2 ] },
  }
},
{$addFields:{
  ageInString: { $substr: [ "$last.age", 0, 3 ] }
  }},
  {$match:{ageInString:{ $regex: '^2' }}}
])

answered Mar 12, 2019 at 9:36

Ashwanth Madhav

1,2341 gold badge12 silver badges23 bronze badges

Collectives™ on Stack Overflow

how to convert query to use regex instead of equal

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related