Does Salesforce CLI data:tree:import command support junction object records in one go?

Question

Suppose A and B are top level objects and C is a junction object having master-detail relationship with A and B. Can i export A, B and C in one go using sfdx:data:tree:export and import in another sandbox using sfdx:data:tree:import?

Answer 1

We currently don't handle junction objects, or record types and maybe a few other things. Developer data import/export/synthesis is high on our road map over the next couple of releases. Basing the export on a query is limiting in a lot of ways. Instead, we are exploring export for schema elements where we processing the relationships, including junctions, as simple queries and then create data plans that can be used on import to handle references.

The idea is that you could execute data:tree:export --schema Contact, Contact_Customobject, Account, CustomObject

And what you get back is separate json files with a data plan to instruct the import command on how to wire it up.

Synthesis would be similar except we would never hit the sandbox or production org to fetch the data. Would love to hear your thoughts on the two approaches.

Answer 2

I've attempted to do so and haven't been able to find a working force:data:tree:export/force:data:tree:import pathway to import and export a junction object and two parent objects in a single operation.

The natural ways to approach this are either to do

$ sfdx force:data:tree:export -u scratch -p \
  -q 'SELECT Id, Name, Parent_Object_2__r.Name, \
   Parent_Object_1__r.Name FROM Junction_Object__c'

that is, approach via the junction object itself and reference both parent objects through that vehicle, or to do

$ sfdx force:data:tree:export -u scratch -p \ 
  -q 'SELECT Id, Name, (SELECT Id, Name, Parent_Object_2__r.Name, \ 
  FROM Junction_Objects__r) FROM Parent_Object_1__c'

that is, to approach via one parent object and obtain the other parent object data by reference through the junction object.

Unfortunately, while both approaches do yield exported data that seems more or less complete (this example is Approach 2)

    {
        "attributes": {
            "type": "Parent_Object_1__c",
            "referenceId": "Parent_Object_1__cRef1"
        },
        "Name": "P1-001",
        "Junction_Objects__r": {
            "records": [
                {
                    "attributes": {
                        "type": "Junction_Object__c",
                        "referenceId": "Junction_Object__cRef1"
                    },
                    "Name": "J-001",
                    "Parent_Object_2__r": {
                        "attributes": {
                            "type": "Parent_Object_2__c",
                            "url": "/services/data/v43.0/sobjects/Parent_Object_2__c/a015B0000078ymqQAA"
                        },
                        "Name": "P2-001"
                    }
                }
            ]
        }
    },

(Note that the second parent object is represented differently and doesn't get a referenceId), importing the same data back does not work. In both cases, running sfdx force:data:tree:import results in

INVALID_FIELD Cannot reference a foreign key field Parent_Object_1__r.

and/or

INVALID_FIELD Cannot reference a foreign key field Parent_Object_2__r.

This applies whether using a plan file or referencing the sObjects files directly.

Interestingly, running

$ sfdx force:data:tree:export -u scratch \
-q 'SELECT Id, Name, (SELECT Id, Name, Parent_Object_2__c \
FROM Junction_Objects__r) FROM Parent_Object_1__c'

doesn't even include the value of Parent_Object_2__c in the output:

    {
        "attributes": {
            "type": "Parent_Object_1__c",
            "referenceId": "Parent_Object_1__cRef1"
        },
        "Name": "P1-001",
        "Junction_Objects__r": {
            "records": [
                {
                    "attributes": {
                        "type": "Junction_Object__c",
                        "referenceId": "Junction_Object__cRef1"
                    },
                    "Name": "J-001"
                }
            ]
        }
    },

The tree commands are built on the sObject Composite REST resource. While I cannot find an explicit statement that one cannot do this, the clearest indicator I see is that the documentation for Create Nested Records states:

Use the SObject Tree resource to create nested records that share a root record type.

The sObject Tree data structure's documentation states

This record’s child relationships, such as an account’s child contacts. Child relationships are either master-detail or lookup relationships.

suggesting that one can descend a tree (parent to child), but cannot ascend it (child to parent) using the Tree API. The Composite API, by contrast, explicitly can achieve this, but it's not used by the SFDX force:data:tree commands.