MongoDB Charts - Lookup Field

Hello there,

I have two collections, let’s say facility and patient. I am trying to create a simple chart, where it shows number of patients per facility(name). Patient’s collection has facilityId and Facility collection has facility name.
I tried to use lookup field to join two collections, but when I am trying to connect id with facilityId, it isn’t populating anything. Just lookup field is being added.

Screenshot 2023-01-13 125952

Any help would be appreciated.

Hi @sunita_kodali -

It’s hard to say what’s going wrong without seeing some example documents from both collections. Your basic approach looks sound, but you’re probably doing something small wrong - can you please send more info?

Tom

Thank you for you response.
Example document:

Facility Table:

_id:ObjectId(‘1234abc’)
organizationId:“12ab”
facilityName:“xyz”
address:""
city:""
state:""
zip:""

Patient Table:

_id:ObjectId(‘xyz123’)
organizationId:“12ab”
facilityId:“1234abc”
firstName:“First”
lastName:“lastTest”

Trying to show on my charts, number of patients per facility name. I was able to do it per facilityId, but not with facility name.
Going over some of your responses, I saw ObjectId cannot be matched with string field. Do you think is that the issue I am going over too?
I also tried to deploy with advanced aggregation pipeline too. Still I couldn’t get what I need.

Thank you,
Sunita

Thanks - yes I think you figured out the problem. If you attempt to use a lookup where one field is a string and another is an Object ID, you won’t get any data. You can use Charts to convert the field in the main collection to the type used in the remote collection, and it should work - although it will be cleaner and faster if you could update your data to use consistent types.

Thanks for your time and support Tom.

Sunita

Morning Tom,

Hope your day is going well. Is there way I can convert all the existing documents field from string to objectId at once? Instead of doing one document at a time.

Thanks,
Sunita

I figured it out with “updateMany” option.

Thank you.

Hello Tom,

Is there a specific syntax for using lookup field in inject user specific filter.
return {userName:context.token.username} here userName is not look up field.
return {organizationId_lookup_organization_userName:context.token.username}, this syntax isn’t working for me.
Any ideas on how to fix this issue?

Thank you,
Sunita

Found the solution. Thank you.

Please share it so that others know. That is the best way to keep this forum useful.

return {“organizationId_lookup_organization.userName”:context.token.username}

lookup field should be in quotes.

1 Like

Hi Tom, I am experiencing the exact same issue but the main collection has a field of int64 which references an _id on another collection with field type of int64. The only doubt I have is that the field on the main collection is not always present. Could this be causing the issue in the lookup?

No that shouldn’t be a problem - it would return the linked document whenever the value existed, and be null/omitted otherwise.

If you can’t figure out why it’s failing, one troubleshooting option is to download the chart’s aggregation pipeline from the … menu and paste into Compass or mongosh where you can try tweaking things to pinpoint the issue.

Tom

@tomhollander thank you so much! Running the aggregation outside of charts yields the expected results i.e., the lookup field contains object when reference is present and null when not. One interesting point to note is that the field in the main collection needed to be manually added under Charts > Fields > + > Missed even though I can see the field on objects in the collection.

Hi @Noah_Wheeler

It’s not a problem that you manually added the field in the main collection as a missed field.

I think the issue might be that this field exists in only a few documents, so when the data source is sampled to show the fields in the Fields Panel in the Chart Builder, none of the sample documents contains the field that you are doing the $lookup on. Hence, you see a “null” value as Tom mentioned above.

One way to prove this theory is to do a filter in the Chart Builder query bar, for example on the existence of the field, and then do the $lookup in the Chart Builder - then the lookup field in the filed panel will not be empty. I realise this might not be what you need for your chart but it will prove the theory that the issue is that the lookup field is missing in a lot of documents. A general workaround is to add this field to all documents using an aggregation if that makes sense for your data.

I hope this helps.
Let us know if you have any other questions.

Thank you, Kristina. Adding the filter to the query bar resolved the issue. Thanks!

1 Like