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.
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?
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.
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.
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.
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?
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.
@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.
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.