What is the proper way to include self-reference lookup field in my query?
Question.
I want to migrate self-referenced account records. I am using the query below, but it does not work for me:
SELECT Name, Parent.External_ID__c, Parent.Id from Account
The External ID key for the Account object is "External_ID__c".
Answer.
You can't use dot in your queries. And you don't need to specify the parent's external id field (External_ID__c).
The proper way to include self-reference lookup fields in your migration is like that:
{
"objects": [
{
"query": "SELECT Name, ParentId from Account", // Include ParentID field as it's the self-refernce field to the same Account object
"externalId": "External_ID__c" // This will set the external Id for Account to External_ID__c
}
]
}
It is the same format like if you want to transfer a lookup of different sObject type (not self-reference).