Make Opportunity SOQL subquery of Contacts return all opportunities in the contact's related list, not just primary ones?

Question

I have Contacts who are associated with multiple Opportunities via multiple OpportunityContactRoles. Some of those OpportunityContactRoles have IsPrimary = true; others don't. In the Lightning UI, in the Opportunity related list for those contacts, I get all the opportunities for which there is an OpportunityContactRole, whether it's primary or not. But when I look up the Contact via SOQL and include an Opportunity subquery, I only get the Opportunities whose OpportunityContactRoles are primary:

SELECT Id, (SELECT Id,Name FROM Opportunities) FROM Contact WHERE Id = '006F0000006B7fwAID'

I know from a previous answer that this is default SOQL behavior, but is there a way to force the SOQL above to return all Opportunities, whether or not their OpportunityContactRoles are primary? In other words, make it return all the opportunities I see in the Lightning UI? Seem fair to expect the UI and the SOQL to return the same thing.

Also, I know I can just add an OpportunityContactRole subquery, which would indeed return all Opportunities including non-primary ones, but they'll be wrapped in an OpportunityContactRole array, and the way our system is built, it's not an option to unpack that array without significant and ugly special-case modifications. I'm looking for an easier way.

Thanks all in advance!

Answer 1

Yes, you are right that using the Opportunities relationship on Contact you will only get the opportunity which has this contact as primary contact. This is enabled via ContactId field on the opportunity.

You will need to query on OpportunityContactRole using to get all opportunities.

Select OpportunityId, Opportunity.Name, ContactId from OpportunityContactRole where Contactid = '006F0000006B7fwAID'