SELECT ALL Fields In A SOQL Query

In Spring 21, Salesforce.com introduced a new function - SOQL FIELDS().

This new function allows us to query all the fields on an object like how SELECT * works in SQL. Earlier you would have to add fields to the select query to get their values but not anymore!

Using FIELDS() helps exceeding the character limits on SOQL queries and the URI length limit for REST calls.

There are three different ways to use the Field Function.

1. FIELDS(ALL) - Selects all of the fields on the object.

2. FIELDS(STANDARD) - Selects all of the standard fields on the object.

3. FIELDS(CUSTOM) - Selects all of the custom fields on the object.

FIELDS() respects field-level security, therefore only the fields that you have permission to access will be shown.

We can run a quick SOQL query in the dev console to retrieve our accounts records by using FIELDS(CUSTOM) and a limit set to 200.

SELECT FIELDS(CUSTOM) FROM ACCOUNT LIMIT 200

This will retrieve the accounts in our org with all of the custom fields selected.

You can also select field names and use FIELDS() in the same query.

SELECT NAME, FIELDS(CUSTOM) FROM ACCOUNT LIMIT 200

There is a limit of 200 records that can be retrieved in queries using the FIELDS() function.

FIELDS() can be used on subqueries as well, if you are querying the Account object and you want to query the associated contacts you could select all fields on the contact object.

SELECT Name, (SELECT FIELDS(ALL) FROM Account.Contacts LIMIT 200) FROM Account

FIELDS() can be used in apex but only if you are using FIELDS(STANDARD) as it is a bounded query and has a well-defined set of fields. Unbounded queries are not supports - FIELDS(ALL) and FIELDS(CUSTOM) in apex.

FIELDS Support

Considerations :

1. You can get errors if you use FIELDS() with operators that require aggregation.

For example, without FIELDS() this query works correctly: SELECT Id, MIN(NumberOfEmployees) FROM Account GROUP BY Id But adding FIELDS() to the query like this SELECT FIELDS(ALL), MIN(NumberOfEmployees) FROM Account GROUP BY Id LIMIT 200results in a “Field must be grouped or aggregated” error because it’s equivalent to SELECT IsDeleted, <etc.>, MIN(NumberOfEmployees) FROM Account GROUP BY Id LIMIT 200.

2. If you already know which fields you want to retrieve, you’ll get better performance by specifying them explicitly rather than using FIELDS() and retrieving more fields than you need.