0

We have an existing Soql query

SELECT Id,Username,LastName,FirstName FROM User 

we are trying to enhance this query to retrieve profile.name, userrole.name corresponding to each user as per How to get the profile name and role name from User object enhanced the existing query to

SELECT Id,Username,LastName,FirstName,profile.name,userrole.name

but we are getting the entire profile object i guess even though we are only querying for profile.name

Profile -> Map(attributes -> Map(type -> Profile, url -> /Profile/testUrl), Name -> Test User)

we are new to Soql and salesforce if you could please point us in the right direction it would be of great help

3

It sounds like you're making this query over the API, right? And the confusion you're seeing is because you get back a nested JSON or XML structure for the Profile object.

That's really just how the Salesforce data model and API work. When you query across a relationship, like Profile.Name, you're not just adding a column like in SQL: you're referencing another object, and that level of structure is represented in the API response. Your client often needs that detail, really, since the data you're working with is natively structured as related objects.

Note, though, that within that additional object structure, you're only getting back the data fields you actually asked for (plus Id if you omitted it). You're not getting back the entire record.

This API query, for example,

GET /services/data/v43.0/query?q=SELECT+Id,+Username,+LastName,+FirstName,+Profile.Name,+UserRole.Name+FROM+User

returns a response that includes JSON like this

{
  "attributes": {
    "type": "User",
    "url": "/services/data/v43.0/sobjects/User/005360000019BXJAA2"
  },
  "Id": "005360000019BXJAA2",
  "Username": "test@gmail.com",
  "LastName": "McTester",
  "FirstName": "Test",
  "Profile": {
    "attributes": {
      "type": "Profile",
      "url": "/services/data/v43.0/sobjects/Profile/00e36000000t7B0AAI"
    },
    "Name": "Customer Community Plus Login User"
  },
  "UserRole": null
}

Note that the JSON structure for Profile is the same as the top-level structure, because its represents that related object.

The only way to remove that additional layer of structure representing the relationship between the objects is to denormalize the data model to place the columns in which you're interested directly on the primary queried object, using formula fields, for example, or either code or declarative automation.

  • Thank you very much response! could i expect similar kind of structure for UserRole if it's available ? – user2359997 Oct 14 '18 at 19:54
  • Yes, and for any other relationship you include in your query (using dot notation). – David Reed Oct 14 '18 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.