SOQL with Big Objects

You can query the fields in a big object’s index by using a subset of standard SOQL commands.

Build an index query, starting from the first field defined in the index, without gaps between the first and last field in the query. You can use = or IN on any field in your query, although you can use IN only one time. You can use the range operations <, >, <=, or >= only on the last field of your query.

When you use the IN clause with only one argument, such as FirstName IN('Charlie'), it’s equivalent to using =, such as FirstName='Charlie'. For clarity, we suggest you use the = form in this case.

Tip

Subqueries aren’t supported. Don’t include more than one select statement in your query. For example, this query is not supported
Select CreatedById, CreatedDate, Created_Date__c, Id, Legacy_Record_ID__c, Parent_Case__c, SystemModstamp, Text_Body__c FROM Archived_Email_Message__b WHERE Parent_Case__c IN(select id from case where owner.id in ('00580000008BBVUAA4'))

You can include the system fields CreatedById, CreatedDate, and SystemModstamp in queries.

To guarantee the order of the query results, use the ORDER BY clause.

These queries assume that you have a table where the index is defined by LastName__c, FirstName__c, and PhoneNumber__c.

This query specifies all three fields in the index. In this case, the filter on PhoneNumber__c can use a range operator.

SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c='Kelly' AND FirstName__c='Charlie' AND PhoneNumber__c='2155555555'

This query specifies only the first two fields in the index. In this case, the filter on FirstName__c can use a range operator.

SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c='Kelly' AND FirstName__c='Charlie'

This query specifies only the first field in the index. The filter on LastName__c can use a range operator.

SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c='Kelly'

This query uses the IN operator on the first field in the index.

SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c IN ('Kelly','Jones','Capulet','Montague') AND FirstName__c='Charlie'

This query doesn’t work because of a gap in the query where FirstName__c is required.

SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c='Kelly' AND PhoneNumber__c='2155555555'
This query also doesn’t work because it uses the IN operator twice.
SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c IN ('Kelly','Jones') AND FirstName__c IN ('Charlie','Lisa')

This query works, even though it appears to have two IN operators in the WHERE clause. But because the second IN has only one argument, it’s equivalent to an equals operator, so it’s allowed.

SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c IN ('Kelly','Jones') AND FirstName__c IN ('Charlie')

For clarity, we suggest that you rewrite the preceding SOQL statement as shown.

SELECT LastName__c, FirstName__c, PhoneNumber__c
FROM Phone_Book__b
WHERE LastName__c IN ('Kelly','Jones') AND FirstName__c='Charlie'

SOQL Operations Not Allowed with Big Objects

  • When building an index query, don’t leave gaps between the first and the last field in the query.
  • The operators !=, LIKE, NOT IN, EXCLUDES, and INCLUDES aren’t valid in any query.
  • Aggregate functions aren’t valid in any query.
  • To retrieve a list of results, don’t use the Id field in a query. Including Id in a query returns only results that have an empty ID (000000000000000 or 000000000000000AAA).

    When you use Developer Console to generate a query from a resource, the Id field is included automatically. To query big objects in Developer Console, remove Id from the generated query.

    Note