A familiar, productive way to explore the shape of your data

For many of us, SQL semantics and data exploration go hand-in-hand. Whether you possess a background in working directly with databases or have developed expertise using SOQL, Salesforce’s own query language, the ability to write a query and get data from it is immensely valuable. That’s why we’re so happy to introduce a new, simple way to explore the data in your Salesforce Objects: the SOQL FIELDS() Function!

The FIELDS() Function enables authors of SOQL statements to designate a predefined grouping of columns that will be automatically returned in the result set of records. FIELDS() provides a way to leverage existing platform constructs that decouples data management from business logic in integrations and helps reduce the effort involved to administer an org’s object model.

Now, anyone writing a query can simply employ a “SELECT FIELDS(ALL)” clause to query all of the fields, standard or custom, on an object. Similar to how “SELECT *” provides the SQL language with a simple way to retrieve data from all the columns in a table, the FIELDS() function in SOQL offers anyone, be they a Developer, Admin, or curious end user, with a scalable, secure way to explore all the fields on an object.

Query more data with less code

In the past, querying data with a field list on an object could be a somewhat complicated process. You would need to write a verbose query if you needed a large number of fields in your result, and if you weren’t aware of all of the fields on an object, you’d have to first understand the shape of that object first, then write your query later. With SOQL FIELDS(), you now have a simple path to diving into your data to extract valuable insights and explore the contents of data on an object.

As an example, let’s say that you worked at a company that produces electric vehicles, and you need to pull data from a custom “Vehicles__c” object that a teammate has built in Salesforce. Before SOQL FIELDS(), you might have had to ask that teammate for insight into the shape of that object, explore its fields in the Object Manager before writing your query, or perform a describe() call. And if you wanted to grab data from almost all of the fields on that object, well, you’d have to write a pretty long SOQL query for that to work. Now, there’s an easier path. With just a simply constructed API call such as:

sfdx force:data:soql:query -q "SELECT FIELDS(ALL) FROM Vehicle__c LIMIT 5" -u DevHub

you’re able to query all fields on the custom “Vehicle” object. You don’t need to bother a teammate or scroll through object Manager – the SOQL FIELDS() Function allows you to jump right in. And if you want to be more precise, such as mix in the FIELDS() Function into a query on a standard object, you have that flexibility too:

SELECT Name, Id, FIELDS(CUSTOM) FROM Account LIMIT 25

With this query, you can pull all of the custom fields on the Account object alongside some standard fields like Name or Id. Likewise, you could employ:

SELECT Subscription__c, FIELDS(STANDARD) FROM Account

To easily grab data from a custom “Subscription” field on the Account object, alongside all standard fields there, too. In any case, you’re writing less code to get more clarity around your data.

As you can see, FIELDS() is a convenient feature that can simplify data exploration use cases. The function can also accelerate platform integrations by promoting loose-coupling between the code developers write and the use cases at-hand while also improving maintainability of the software. For example, rendering data for a dynamic and ever-changing object model can result in on-going iterations of the underlying queries or extra round-trips to the server to perform a describe call to ensure no fields are left behind. The FIELDS() function is aware of bounded and unbounded groupings of columns to help safeguard performance. A well-designed deployment of FIELDS() will optimize the resources required to retrieve the data with those involved in processing and working with the results in order to best support the given use case.

Making SOQL queries simple, scalable, and secure

The FIELDS() Function is the latest enhancement to the SOQL language, and a fantastic complement to other recent changes you may have missed, all of which make Salesforce data access easier and more powerful than ever. First, we recently updated the SOQL character limits from 25K characters to 100K characters, empowering query authors to be more complete with their query operations. With the new character limits and FIELDS(), we continue to reinforce our practice of reducing friction when operating with data on the Salesforce Platform, while also empowering you to get more done with less code, calls, or characters – all of which we spoke of last year in our TDX20 API session.

We’ve also recently introduce the SOQL Query Builder, a brand new VS Code plug-in now in Beta that makes it easy to write and execute queries right from VS Code. For Developers, this means less tool and context switching in their work, and for many Admins or business users, it indicates a lower barrier to entry to intuitively working with your Salesforce data. As the SOQL Query Builder is in Beta, we’re just starting to scratch the surface on easier ways for you to work with your data, but we welcome your feedback and would encourage you to pay attention to some exciting things happening in this space.

Last, but certainly not least, the FIELDS() Function is extensible and secure. While similar in nature to “SELECT *” as mentioned earlier, you’ll notice Salesforce security standards, such as the enforcement of field-level security within your query API operations, which ensure that users only see results that respect their data access privileges. We’re also designing FIELDS() as a query operation that will scale to support new use cases in the future, so if the prospect of more intuitive ways to further leverage predefined groupings (or even sets) of fields on the platform excites you, then you’ll want to stay tuned to this space and keep the feedback coming!

The SOQL FIELDS() Function is Generally Available as of the Spring ’21 release. For specific usage information and performance considerations, please refer to the FIELDS() topic within the SOQL Developer Guide.

About the Author

Kris Harrison is Director of Product Management for APIs, SOQL, and External Services at Salesforce.

Complete a Learn MOAR Spring ’21 trailmix for Admins or Developers by March 31, 2021 to earn a special community badge and enter for a chance to win 1 of 5 $200 USD Trailhead Certification vouchers. See Official Rules below.

Share what you love about the Spring ’21 Release with the #LearnMOAR hashtag. Also, don’t forget to sign up for Release Readiness Live happening on January 29, 2020.