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).

Last updated on 17th May 2023