MongoDB Project Conditional Field with Value From list

Matias Ordoñez 0
Viewed 20

i need to project a new field called "operator" in the ref_docs collection using the ref_operators value if answer_text contains the id , and if not exists , the value should be 'Not Found' , using the agreggation framework in Mongodb

Ex:

 ref_operators={
                 {id:'Jorge',value:'Jorge'},
                 {id:'Juan',value:'Juan}'
                }
 ref_docs ={
            {
             date_created:'2022-02-02',
             answer_text:'Hello Jorge'
            },
            {
             date_created:'2022-02-02',
             answer_text:'Hello Juan'
            },
            {
             date_created:'2022-02-02',
             answer_text:'Hello Carlos'
            },
            {
             date_created:'2022-02-02',
             answer_text:'Hello Roberto'
            }                
           }

Expected output will be:

ref_docs ={
                {
                 date_created:'2022-02-02',
                 answer_text:'Hello Jorge',
                 operator:'Jorge'
                },
                {
                 date_created:'2022-02-02',
                 answer_text:'Hello Juan',
                 operator:'Juan'
                },
                {
                 date_created:'2022-02-02',
                 answer_text:'Hello Carlos',
                 operator:'Not Found'
                },
                {
                 date_created:'2022-02-02',
                 answer_text:'Hello Roberto',
                 operator:'Not Found'
                }                
               }
mongodb
1 Answers

Query

  • lookup and regex match answer_text with value
  • if empty results => no match => not found
  • else take the value of first member as operator

Playmongo

ref_docs.aggregate(
[{"$lookup": 
   {"from": "ref_operators",
    "pipeline": 
     [{"$match": 
         {"$expr": {"$regexMatch": {"input": "$$text", "regex": "$value"}}}},
       {"$project": {"_id": 0, "value": 1}}],
    "as": "operator",
    "let": {"text": "$answer_text"}}},
 {"$set": 
   {"operator": 
     {"$cond": 
       [{"$eq": ["$operator", []]}, "Not Found",
         {"$getField": 
           {"field": "value", "input": {"$first": "$operator"}}}]}}}])
Takis0
Related