sfdx force:data:tree:export not returning all fields

Question

I have two custom objects on my scratch org:

Role__c

• fields...

Role_Permission_c

• Role__c (Lookup(Role))
• Name (Text(80))
• fields...

I would like to get all records from both objects. So I executed:

sfdx force:data:tree:export -q "SELECT Role__c, Name FROM Roles_Permission__c"

But I only get the field 'Name' in return

{
"records": [
    {
        "attributes": {
            "type": "Roles_Permission__c",
            "referenceId": "Roles_Permission__cRef1"
        },
        "Name": "delete_products"
    },
    {
        "attributes": {
            "type": "Roles_Permission__c",
            "referenceId": "Roles_Permission__cRef2"
        },
        "Name": "read_products"
    },
    {
        "attributes": {
            "type": "Roles_Permission__c",
            "referenceId": "Roles_Permission__cRef3"
        },
        "Name": "add_products"
    }
  ]
}

I executed my SOQL Query on the workbench and also on the dev console. I got the field 'Role__c' as well.

I also tried this SOQL

sfdx force:data:tree:export -q "SELECT (SELECT Name From Roles_Permission__r), Name FROM Roles_Permission__c"

But I got an error

Didn't understand relationship 'roles_permission__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate name

How do I get Role__c from Role_Permission_c?

Answer 1

Your outermost SELECT needs to be against your parent object, Roles__c:

SELECT (SELECT Name From Roles_Permission__r), Name FROM Roles__c

Then, your subquery may select the child objects, the Roles_Permission__c records. Note that the relationship name must be the one configured on the lookup field from the child to the parent, and it may not be the same as the sObject name with __r - it's often the plural name.