Database.Query does not return fields with null value

Question

Hi i'm executing this query using

Database.Query(query)

Where query is

SELECT field1__c, field2__c, field3__c FROM Object1__c

On the current record, field3__c is NULL but when i query on the record instead of getting

"field1__c = value1", "field2__c = value2", "field3__c = null"

I get

"field1__c = value1", "field2__c = value2"

Just like the field has been deleted/not returned, which is a problem for our purpose.

The question is:

How can i get the null values returned as NULL or BLANK and not just "not returned"?

Answer 1

If you are checking in the debug logs, then that is how it will be displayed. It behaves the same for the normal SOQL query as well as for the dynamic SOQL i.e. with Database.Query

So, if you perform a SOQL on Object1__c with field3__c as null, then the debug will always show you

USER_DEBUG [2]|DEBUG|Object1__c:{Id=0010o00002GxDELAA3, field1__c=CD355119-A, field2__c=1231313}

irrespective of whether you used normal SOQL or Database.Query. If you want to add a null check for field3__c, then you can do

List<Object1__c> objList = Database.Query('SELECT field1__c, field2__c, field3__c FROM Object1__c');
for(Object1__c obj : objList){
    //check for null for field3__c
    if(obj.field3__c != null){
    }
}

If you are passing this field3__c which to a flow i.e. a formula field, then you should have an additional check for related lookup field i.e. if your formula field's formula is Account.AccountNumber then have a check for AccountId != NULL && field3__c != NULL in flow. If you try to directly access field3__c or check for NULL, it will give you error. So always have an additional check for its lookup field as first check and then check for its related field.

Answer 2

maybe it's late for my answer, but I was able to resolve this by initializing those fields manually after the SOQL call... just like this

LiveChatTranscript lct = [SELECT Id, ContactId, AccountId, Name FROM LiveChatTranscript WHERE Id =: lctId];
//Initialize ContactId and AccountId in case the SOQL query did not return the field because they are set to null
lct.ContactId = lct.ContactId;
lct.AccountId = lct.AccountId;
Flow.Interview flow = new Flow.Interview.Case_Create_CSAT_Survey(new map<String, Object> {'ChatTranscript' => lct});     
flow.start();

In my case, I was using those field in a flow and this flow is also called from process builder where we sent the LiveChatTranscript record so the field is there. In the apex, it's being called in a future method so had to retrieve it manually, therefore just initializing the fields with themself worked.