How to make a join between two string fields with contains method?

Working with Data
,
1

I need a help to write a query and I would like aggregate function.
The scenario I’m giving is an example but it is very similar to my real scenario.

I have a collection whose name is “corporate_composition”.
Follow same data examples:

{
id: 1,
associate_name: partner_a,
corporate_name: Coca-cola
}
{
id: 2,
associate_name: partner_b,
corporate_name: Coca-cola
}
{
id: 3,
associate_name: partner_c,
corporate_name: Ford
}

And I have another collection whose name is “corporate_goods”.
Follow same data examples:

{
id: 1,
composition: 100% for Coca-cola,
name_goods: build_1
}
{
id: 2,
composition: 59% for Coca-cola and 41% for Ford,
name_goods: build_2
}
{
id: 3,
composition: 100% for Ford,
name_goods: build_3
}

How I made a query with aggregate to get goods using the field “corporate_composition.corporate_name”
and “corporate_goods.composition”?

Using SQL is like that:

SELECT corporate_goods.*
FROM corporate_goods, corporate_composition
WHERE corporate_goods.composition LIKE ‘%’||corporate_composition.corporate_name||’%’

I tried to use lookup operator but the clausule ‘localField’ and ‘foreignField’ need to have the same value.
I saw that have the clausule ‘let’ for create variables to clausule ‘pipeline’ but I can’t use it.

Somebody can to write a exemple to resolve my question?

Thanks

2

Please read Formatting code and log snippets in posts and then update your sample documents so that we can cut-n-paste them into our system and experiment potential solution to your issue.

You are right about not being able to use localField and foreignField since your LIKE %…% and your free form text of the field composition will require $regex. Regex are slower than direct comparison. In your case a simple schema change might be in order.

I would make the free form text field composition an array. Each element would become a tuple of corporate name and part percentage. So the corporate_goods collection could look like

{   id:1 ,
    composition : [
        { corporate : Coca-cola , percent : 100 }
    ]
    name_goods : ...
}
{   id:2 ,
    composition : [
        { corporate : Coca-cola , percent : 59 } ,
        { corporate : Ford , percent : 41 }
    ]
    name_goods : ...
}
...

No regex and probably can use localField,foreignField