Charts lookup fields

[Sergi_Martinez]

I have a specific question about charting that maybe you can answer me. I have a question in regards to lockup fields. The question is the following:

• We have 1 database with 10 collections.
• When I use the lookup field to get data from one collection to the other, I have managed to make it work but not always.
• I was under the impression that I could use the lookup field when the field value from one collection matched the field value from the other collection.
• However, I believe this is not true because for some other fields I struggle to do the lookup field correctly.
• I have noticed that when I fail to do the lookup field is because the file in one collection is an object id whereas the field in the other collection is a string.
• Question: How can I do the lookup field when 2 fields have the same values but have different field types?

[tomhollander]

Hi @Sergi_Martinez -

Lookup fields use the MongoDB $lookup aggregation stage under the covers, and this does a strict equality check against the field values, so a string and an Object ID will never match.

There are a couple of directions you could consider:

1. Convert the field type in the parent collection prior to the Lookup being performed. You can’t use the Charts “Convert Type” in this case, as we decided to allow type conversions after lookups but not the reverse. But you could do the same in the query bar using a query like [{$set: { fieldName: { $toString: '$fieldName' } } }]
2. Rather than use the Lookup Field feature in the chart builder, add a pipeline to the data source that uses the advanced syntax of $lookup that allows you to specify custom join conditions.

Let me know if either of these work.
Tom