CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Alexander F
Alexander F

Reputation: 48

How to do a regex query on JSONS inside an array

I would like to do a $regex query on a field inside the $match part of the aggregation framework

Here is an example document from the collection:

{
    "_id": ObjectId("5ce6bfea63519d2f77014dcc"),
    "lead_name": [
        {
            "Bill": "Toz"
        }
        ,
        {
            "Gordon":"Banks"
        }
    ],
    "lead_email": [
        {
            "email": "[email protected]"
        }
    ],
    "lead_phone_number": [
        {
            "phone_number": "+148034543"
        }
    ],
    "lead_country": [
        {
            "country": "US"
        }
    ],
    "lead_city": [
        {
            "city": "Phoenix"
        }
    ],
    "lead_team_id": "5cd98dd163519d6dd94aff12",
    "lead_source_id": "5cd98c9d63519d61432db1c5",
    "lead_status_id": "5cd98c9e63519d61432db387",
    "lead_assigned_to_user_id": "",
    "created_on_timestamp": 1558625945,
    "last_updated_on_timestamp": 1558625945,
    "lead_last_interaction_timestamp": 1558625945,
    "lead_last_interaction_subject": "Inbound call - answered",
    "lead_products": [],
    "lead_metadata": []
}

I want that if the query value is "Bill" it will return me this document

Upvotes: 1

Views: 48

Answers (1)

Plancke
Plancke

Reputation: 1139

I got this to work with the following pipeline, though I don't recommend storing data in this fashion as you'll run into querying issues like this. https://mongoplayground.net/p/R_I5_J1KeiZ

db.collection.aggregate([
  {
    $match: {
      "lead_name": {
        $elemMatch: {
          "Bill": {
            $ne: null
          }
        }
      }
    }
  }
])

If you can I'd suggest changing your data to be more like the following. It might also be better to define these 'lead' users once in a different collection, and then reference them using an identifier.

I don't know what this data is really storing though so this might not be a good solution for your use case, but figured I might aswell share an idea.

"lead_name": [
  {
    "firstname": "Bill",
    "lastname": "Toz",
    "email": "[email protected]",
    "phone_number": "+148034543",
    "country": "US",
    "city": "Phoenix"
  },
  {
    "firstname":  "Gordon",
    "lastname": "Banks"
  }
],

Upvotes: 1

Related Questions