SOQL LIKE operator on picklist field appears to fetch results based on matches on label not API value

Question

SOQL LIKE operator on picklist field appears to fetch results based on matches on label not API value.

For example, take this query

SELECT Id, Name, RZone__Type__c FROM RZone__PSPayment__c
WHERE RZone__Type__c LIKE ‘%Migration%’

Here RZone__Type__c is a picklist

This query returns records with a Type API value of “Advance” because the label is “Balancing Payment Data Migration”. I would expect to see the like operator applied to the API value and not the label. From the documentation The LIKE operator can only query on the label of the picklist, not on its API name - This limitation applies only to translated picklist values. The doc doesn’t say why does the like operator queries on the labels even though translation used

I came across this post where it said If you have a scenario where LIKE needs to match the API name, you could create a formula field targeting the same picklist. Formula fields always return API name not label.

Can anyone please advise if this is working as designed? Thanks for your inputs

Answer 1

Turns out there isn't a way to filter based on API name when the operator is LIKE unfortunately. This is a limitation today

The following limitations apply to translated picklist values: ・The LIKE operator can only query on the label of the picklist, not on its API name.

As per this documentation , If no translation is available for the picklist, the comparison is made against the master values.