How to do a regex query on JSONS inside an array

Question

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

The data structure is really questionable, if it is early in the project I would recommend restructuring your models, updating models would be easy as well. Check answer from Plancake for the structure. — noitse
– noitse, Commented May 25, 2019 at 16:17
@noitse - thank you for your comment. Each lead can have mutltiple names and I didn't want to create seperate array of objects for first_name and last_name as maintaining it (deleting, editing, etc.) might be problematic so I settled on this less then perfect structure. — Alexander F
– Alexander F, Commented May 26, 2019 at 8:31

Plancke · Accepted Answer · 2019-05-25 14:18:27Z

1

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"
  }
],

answered May 25, 2019 at 14:18

Plancke

1,13911 silver badges12 bronze badges

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

5 Comments

Alexander F Over a year ago

thanks for your answer. As I have already written in my comment above each lead can basically have multiple cities, phone numbers, emails and names. To avoid the problems that might arise with managing first_name and last_name separately I've settled on this less than perfect structure. Regarding your query - how can I return the document if the lead_name query value equals to "Gordo" (I want to a regex match here)? Thanks, Alexander

Plancke Over a year ago

You could just replace Bill with Gordon in the query, but a regex search on key isn't possible in mongo. You can only do exact matches with the format you have provided.

Plancke Over a year ago

As for the data structure, the one you have currently will only give you problems, like with this query but more will follow most likely. There should be a better structure out there. If you can still change it without too much hassle I heavily recommend it.

Alexander F Over a year ago

data structure changed per your advice.

Plancke Over a year ago

You can still make each lead have multiple addresses/phone numbers by just making the fields array as well

Collectives™ on Stack Overflow

How to do a regex query on JSONS inside an array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related