SOQL Lookup failing to retrieve values

Question

I have following query that I am trying to run:

select X__Region__r.Name,  Operating_Unit__c from X__Worker__c where (X__Worker_Status_Current_WA__c = 'Active')

Where X__Region__c is a lookup to the Region table and Name is the value that I trying to retrieve. But I am getting this value returned:

OrderedDict([(u'attributes', OrderedDict([(u'type', u'X__Region__c), (u'url', u'/services/data/v29.0/sobjects/X__Region__c/aKR0O0000008OIKWA2')])), (u'Name', u'AMER')])

The value that I am hoping to get is in the string above - AMER but the way it is being displayed is not correct as you can see. How do I modify my query?

Answer 1

That's just how the Salesforce API returns cross-relationship data (as unpacked into OrderedDict structures in Python).

It looks like you tried to get the property X__Region__c from the top-level object that you queried, because the content you're seeing here:

OrderedDict([(u'attributes', OrderedDict([(u'type', u'X__Region__c), (u'url', u'/services/data/v29.0/sobjects/X__Region__c/aKR0O0000008OIKWA2')])), (u'Name', u'AMER')])

is actually the representation of a nested object - specifically, the related X__Region__c object. You need to descend another level in your data structure to access its Name field.

If you run a query from the API and look at the entire raw JSON response, it'll be more illuminating. Here's an example.

GET /services/data/v43.0/query/?q=SELECT+Id,+Name,+Account.Name+FROM+Contact+LIMIT+1

{
  "totalSize" : 1,
  "done" : true,
  "records" : [ {
    "attributes" : {
      "type" : "Contact",
      "url" : "/services/data/v43.0/sobjects/Contact/0033600001aXXXXAAA"
    },
    "Id" : "0033600001aXXXXAAA",
    "Name" : "Jim Testerson",
    "Account" : {
      "attributes" : {
        "type" : "Account",
        "url" : "/services/data/v43.0/sobjects/Account/0013600001eXXXXAAI"
      },
      "Name" : "Testcorp"
    }
  } ]
}

Note how the Account data is nested inside a second level JSON object, whose structure is the same as the top-level Contact object you requested. The OrderedDict structure used by, e.g., simple_salesforce in Python mirrors this structure, and it applies anywhere you're using dotted relationship paths in this way.

This is one of many areas of Salesforce where insights from SQL will lead you astray. The API returns structured JSON, not a flat table.