Performing regex queries with PyMongo issue

Question

I am trying to perform a regex query using PyMongo against a MongoDB server. The document structure is as follows.

{
    "_id" : ObjectId("6076d2598128b279dfd62b1d"),
    "transactionId" : "Test_Operation_112",
    "requestA" : {
            "pState" : "{\"key1\":\"value1\",\"key2\":\"value2\"}",
            "request" : "{\"header\":{\"sender\":{\"login\":\"XXXXXXXXXX\",\"password\":\"XXXXXXXXXX\"},\"notification\":{\"url\":\"https://example.com/pqr\"},\"transactionId\":\"Test_Operation_112\",\"timeStamp\":\"04/13/2021 12:30:25-07:00\"},\"order\":{\"action\":\"CANCEL\"}]}}}"
    },
    "requestTimeStamp" : "04/13/2021 12:30:25-07:00",
    "responsesA" : {
            "response" : "<?xml version='1.0' encoding='UTF-8' standalone='yes'?><Response System='HGGH' xsi:noNamespaceSchemaLocation='Response.xsd' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'><Header TransactionId='Test_Operation_112'  TimeStamp='04/13/2021 12:30:25-07:00'><Sender Login='XXXXXXXXXX' Password='XXXXXXXXXX'/><Notification URL='https://example.com/pqr'/><TransactionCode MajorCode='0' Description='Success'><TransactionCodeList ErrorCode='0' ErrorMessageText='Service Removed'/></Response>"
    },
    "lastUpdatedTime" : ISODate("2021-04-14T11:30:33.605Z")
}

{
    "_id" : ObjectId("6076d2598128b279dfd62b1d"),
    "transactionId" : "XYZ123123_7_Test$a1b2c3",
    "requestA" : {
            "pState" : "{\"key1\":\"value1\",\"key2\":\"value2\"}",
            "request" : "{\"header\":{\"sender\":{\"login\":\"XXXXXXXXXX\",\"password\":\"XXXXXXXXXX\"},\"notification\":{\"url\":\"https://example.com/pqr\"},\"transactionId\":\"XYZ123123_7_Test$a1b2c3\",\"timeStamp\":\"04/13/2021 12:30:25-07:00\"},\"order\":{\"action\":\"CANCEL\"}]}}}"
    },
    "requestTimeStamp" : "04/13/2021 12:30:30-07:00",
    "responsesA" : {
            "response" : "<?xml version='1.0' encoding='UTF-8' standalone='yes'?><Response System='HGGH' xsi:noNamespaceSchemaLocation='Response.xsd' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'><Header TransactionId='XYZ123123_7_Test$a1b2c3'  TimeStamp='04/13/2021 12:30:25-07:00'><Sender Login='XXXXXXXXXX' Password='XXXXXXXXXX'/><Notification URL='https://example.com/pqr'/><TransactionCode MajorCode='0' Description='Success'><TransactionCodeList ErrorCode='0' ErrorMessageText='Service Removed'/></Response>"
    },
    "lastUpdatedTime" : ISODate("2021-04-14T11:30:33.605Z")
}

There are different types transaction ID. I want to get all the records that match the pattern for transaction ID. Regex pattern is correct. I tried doing this as such

db.collectionName.find({"$and": [
{"requestTimeStamp": {"$gte": '04/13/2021 00:00:00-00:00', "$lte": '04/13/2021 23:59:59-00:00'}},
{"responsesA.response": {"$regex":  "ErrorCode='0'"}},
{"requestA.request": {"$regex": "CANCEL"}}, 
{ "transactionId": { "$in": [/^Test_Operation_\d*/, /^XYZ\d*_\d_Test\$\S*/] } }]});

Yet I get nothing back. Am I missing something, because according to the MongoDB docs this should be possible? If I perform the query in the Mongo console it works fine, does this mean the API doesn't support it or am I just using it incorrectly?

requestTimeStamp is of type string. If I remove below part then query is executed successfully in python as well. { "transactionId": { "$in": [/^Test_Operation_\d*/, /^XYZ\d*_\d_Test\$\S*/] } } — Aks.Soms
– Aks.Soms, Commented Apr 23, 2021 at 15:33

Belly Buster · Accepted Answer · 2021-04-23 20:26:52Z

1

Several things. find() returns a cursor; you have to iterate it to get the results. You don't need the $and part as queries are anded by default. Your regex can be simplified (see code).

Putting it together:

cursor = db.collectionName.find(
    {"requestTimeStamp": {"$gte": '04/13/2021 00:00:00-00:00', "$lte": '04/13/2021 23:59:59-00:00'},
     "responsesA.response": {"$regex": "ErrorCode='0'"},
     "requestA.request": {"$regex": "CANCEL"},
     "transactionId": {'$regex': '^Test_Operation_\d*|^XYZ\d*_\d_Test\$\S*'}})

print(list(cursor))

answered Apr 23, 2021 at 20:26

Belly Buster

8,9142 gold badges12 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Performing regex queries with PyMongo issue

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