Query problem: how to get the matching items of the $or operator

Working with Data
1

Thank you for first.

MongoDB Version:4.2.11

I have a piece of data like this:

{
 "name":...,
 ...
 "administration" : [
    {"name":...,"job":...},
    {"name":...,"job":...}
  ],
 "shareholder" : [
    {"name":...,"proportion":...},
    {"name":...,"proportion":...},
  ]
}

I want to match some specified data through regular expressions: For a example：

db.collection.aggregate([
  {"$match" : 
   {
     "$or" : 
     [
       {"name" : {"$regex": "Keyword"}}
       {"administration.name": {"$regex": "Keyword"}},
       {"shareholder.name": {"$regex": "Keyword"}},
     ]
   }
  },
])

I want to set a flag when the $or operator successfully matches any condition, which is represented by a custom field, for example: {"name" : {"$regex": "Keyword"}} Execute on success:

{"$project" : 
        {
            "_id":false,
            "name" : true,
            "__regex_type__" : "name"
        }
    },

{"administration.name" : {"$regex": "Keyword"}} Execute on success: "__regex_type__" : "administration.name"

I try do this:

{"$project" : 
            {
                "_id":false,
                "name" : true,
                "__regex_type__" : 
                {
                   "$switch":
                        {
                            "branches":
                            [
                              {"case": {"$regexMatch":{"input":"$name","regex": "Keyword"}},"then" : "name"},
                              {"case": {"$regexMatch":{"input":"$administration.name","regex": "Keyword"}},"then" : "administration.name"},
                              {"case": {"$regexMatch":{"input":"$shareholder.name","regex": "Keyword"}},"then" : "shareholder.name"},
                            ],
                            "default" : "Other matches"
                        }
                }
            }
        },

But $regexMatch cannot match the array,I tried to use $unwind again, but returned the number of many array members, which did not meet my starting point.

I want to implement the same function as mysql this SQL statement in mongodb, like this:

SELECT name,administration.name,shareholder.name,(
 CASE
 WHEN name REGEXP("Keyword") THEN "name"
 WHEN administration.name REGEXP("Keyword") THEN "administration.name"
 WHEN shareholder.name REGEXP("Keyword") THEN "shareholder.name"
 END
)AS __regex_type__ FROM db.mytable WHERE 
  name REGEXP("Keyword") OR
  shareholder.name REGEXP("Keyword") OR
  administration.name REGEXP("Keyword");

what should I do? Confused me for a long time.
Maybe this method is stupid, but I don’t have a better solution.
If you have a better solution, I would appreciate it!!! Thank you!!!

1 Like
2

Hello @binn_zed, welcome to the MongoDB Community forum.

You can use $facet stage to match each of the conditions and project the result like in the following aggregation.

db.test.aggregate([
  {
    $facet: {
        name_match: [
            { $match: { name : { $regex: "..." } } },
            { $project: { name: 1, __regex_type__: "name"  } }
       ],
       admin_name_match: [
            { $match: { "administration.name": { $regex: "..." } } },
            { $project: { name: 1, __regex_type__: "admin_name"  } }
       ]
    }
  },
  { 
    $project: {
        result: {
            $switch: {
                branches: [
                        { case:  
                             { $gt: [ { $size: "$name_match" }, 0 ] }, 
                             then: { $arrayElemAt: [ "$name_match", 0 ] } 
                        },
                        { case: 
                             { $gt: [ { $size: "$admin_name_match" }, 0 ] }, 
                             then: { $arrayElemAt: [ "$admin_name_match", 0 ] } 
                        }
                ],
                default: "Other Matches"
            }
       }
    }
  },
]).pretty()

This prints an output like this:

{
        "result" : {
                "_id" : 1,
                "name" : "john",
                "__regex_type__" : "admin_name"
        }
}

This gives the required output, I think. You can also use other ways to project after the $facet stage.

3 Likes
3

hey.

First thank you

After reading your answer, I am first convinced by your wisdom, This is really beautiful
But the $switch behind the latter $project only limits the last result set, which is not what I want. In the end, I did it like this.

db.base.aggregate([
  {"$match" : 
   {
 "$or" : 
 [
   {"name" : {"$regex": "..."}},
   {"administration.name": {"$regex": "..."}},
  {"shareholder.name": {"$regex": "..."}},
 ]
   }
  },
  {
  $facet: {
    name_match: [
        { $match: { name : { $regex: "..." } } },
        { $project: { name: 1, __regex_type__: "name"  } }
   ],
   admin_name_match: [
        { $match: { "administration.name": { $regex: "..." } } },
        { $project: { name: 1, __regex_type__: "admin_name"  } }
   ],
   shareholder_name_match: [
        { $match: { "shareholder.name": { $regex: "..." } } },
        { $project: { name: 1, __regex_type__: "shareholder_name"  } }
   ]
}
  },
  { 
$project: {
    "name_match":true,
    "admin_name_match":true,
    "shareholder_name_match":true
}
  },
])

t
This will return a matching array for each corresponding item, but I prefer it not in an array format, but in this format:

**_id  name  __regex_tyepe__**

Only add one string of matching type per line

Anyway thank you :blush:

1 Like
4

And how to limit the result set in each $facet, for example, I need to limit the total number of records to 10, ie if I use {$limit 10} in the $facet, it will become the result of the limit per condition, not all Sum of results

Thanks again

5

Here is the solution:

db.test.aggregate([
  { 
    $match : {
      $or : [
          { name : { $regex: "…" }},
          { "administration.name": { $regex: "…" }},
          { "shareholder.name": { $regex": "…" }},
      ]
    }
  },
  {
    $facet: {
        name_match: [
           { $match: { name : { $regex: "..." } } },
           { $project: { name: 1, regex_type: "name" } }
        ],
        admin_name_match: [
           { $match: { "administration.name": { $regex: "..." } } },
           { $project: { name: 1, regex_type: "admin_name" } }
        ],
        shareholder_name_match: [
           { $match: { "shareholder.name": { $regex: "…" } } },
           { $project: { name: 1, regex_type: "shareholder_name" } }
        ]
    }
  },
  { 
    $project: { 
        result: { 
            $concatArrays: [ "$name_match", "$admin_name_match", "$shareholder_name_match" ] 
        }
    }
  },
  { 
      $unwind: "$result" 
  },
  { 
      $replaceRoot: { newRoot: "$result" } 
  }
])

The output looks like this:

{ "_id" : 2, "name" : "pete", "regex_type" : "name" }
{ "_id" : 1, "name" : "john", "regex_type" : "admin_name" }
{ "_id" : 3, "name" : "jim", "regex_type" : "admin_name" }

Add the $limit stage after the initial $match stage (before the $facet stage).

6

Sir, it is an honor to receive your suggestions. This solved my problem.

I added $limit 10 after $match, and the result still returns> 10 data

3
3853×572 22.3 KB

I do not know why.

7

i got it.
Add $limit10 to the end of the aggregate :smile:

8

That is because a document may have more than one match - e.g., name match and admin match (you can know this by verifying the _id field’s value). When there is more than one match, they show as multiple documents in the result.

9

I have one more question for you. This is very simple for you. What if I need to sort the matches?
For example: I need to match the matching degree to sort:
1.name: “keyword” -> exact match
2.$regex: “^keyword” -> match at the beginning of the line
3.$regex: “.+keyword.+” -> middle match
4.regex: "keyword " -> match at the end of the line

Then according to the matching degree in ascending order, from small to

10

Yes, I see that you want to sort on matching degree; that may be possible. I don’t have a clear thought about it right away. How would you go about it, for example, in pseudo-code?

11

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.