How to extract only profile.name and userrole.name corresponding to a particular User object

Question

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

Answer 1

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": "[email protected]",
  "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.