REST - how to get item ID and document name while filtering by a Lookup field?

Question

I am trying to query a document library via REST and get the item ID and the document name. The query needs to filter on a (single value) lookup field.

Originally I was using a CAML query to do the filtering on the lookup field, which worked, but that only returned SPListItem objects, which have the ID, but that means I would need to do another REST call to retrieve the SPFile objects to get the document name.

I came across this answer which solved the problem of getting the ID and document name with one call by using OData $select and $expand options on the FieldValuesAsText property.

I also came across this other answer about how to filter on a Lookup field if you know the lookup ID (which I do). It basically says that when a Lookup field is added to a list, the lookup IDs are accessible via a property named LookupFieldName + Id, so: MyLookupFieldId.

My REST query looks like this:

"/_api/web/lists/getbytitle('Doc Lib Name')/items?$select=ID,FieldValuesAsText/FileLeafRef&$expand=FieldValuesAsText/FileLeafRef&$filter=MyLookupFieldId eq " + lookupId;

However, the filter is not working. I get an error:

Column 'MyLookupFieldId' does not exist. It may have been deleted by another user.

Oddly, if I do $select=ID,MyLookupFieldId,FieldValuesAsText/FileLeafRef with no filter, the response does contain a property named MyLookupFieldId with the correct lookup ID.

So what's going wrong here?

I found yet another answer which has a comment that seems to imply that filtering on a lookup field by ID is not supported on-prem, but that question is also about multi-lookup fields, and mine is a single value lookup field.

So is that the case? Is OData filtering on a Lookup field not supported on-prem regardless of single or multi lookup?

If so, how can I get what I want? Is it possible to combine a CAML query and OData query so I can use CAML to filter by the lookup field, but OData to expand the FieldValuesAsText/FileLeafRef to get the document name?

How can I get the SPListItem ID and the SPFile document name, while filtering by a Lookup column, all in one single REST query?

Answer 1

Ugh, yet again, another one of those times when you discover the answer yourself a mere five minutes after posting the question.

It works if you explicitly expand the lookup field you are trying to filter on, like so:

"/_api/web/lists/getbytitle('Doc Lib Name')/items?$select=ID,MyLookupField/Id,FieldValuesAsText/FileLeafRef&$expand=FieldValuesAsText/FileLeafRef,MyLookupField&filter=MyLookupField/Id eq " + lookupId;

Answer 2

When filtering, you take the Id off of the lookup (ONLY in the filter). I use the same syntax as your original example in your question, but filter should look like: ..."&$filter=MyLookupField eq " + lookupId;. I'd suggest using that rather than expanding it.