Force Dynamic Soql results to include fields with null value

Question

So I've read a lot about this and it seems that queries run with Database.query(queryString) will skip over any field values set to null instead of returning the property set to null

This has cause a TON of problems in our code and I wondered if anyone knows a workaround.

We are using Dynamic SOQL to output queries as json and did a simple test to see where the values were being stripped out. I originally assumed it was the JSON.serialize() but it appears that it is Database.Query

Here is what we have

public String getJson() {

        List<sObject>records = Database.query('Select Name, Site__c from Customer__c Limit 5');

        Map<String, Object> result = new Map<String, Object>();
        result.put('records', records);
        result.put('something', null);
        return JSON.serialize(result);
    }

Which outputs like this

"records":[{"attributes":{"type":"Customer__c","url":"/services/data/v33.0/sobjects/Customer__c/a0HK000000CxwVjMAJ"},"Name":"fdgdfgd","RecordTypeId":"012K000000053jlIAA","Id":"a0HK000000CxwVjMAJ"},{"attributes":{"type":"Customer__c","url":"/services/data/v33.0/sobjects/Customer__c/a0HK000000CxwVtMAJ"},"Name":"asdf","RecordTypeId":"012K000000053jqIAA","Id":"a0HK000000CxwVtMAJ","Site__c":"a1PK0000001e9AQMAY"}],"something":null}

And you can see the "something" outputs as null just fine but the first customer is missing the Site__c property all together

any suggestions?

Answer 1

For me too, this behavior caused a lot of trouble.

I found not much clear documentation, but my guess is that the omission of null values is an optimization strategy of salesforce. In most cases this omission has no impact, but in lots of cases it saves memory as a very limited resource of the platform.

After some research best thing I got are the answers here: Apex JSON.serialize() with null values (RELOADED) - which cleard the backgrounds but actually didn't solve my problems.

Now my workarounds for these issues are

• either to use schema methods to generate a list of all fields
• or generate a list of fields (to be selected) manually

In both cases I have a List<String> of field-names. Then I create the SOQL string (from the list), execute my query and receive the sparse results.

Now I still have the field-list created before. Depending on the use case I either use this list for iterations in Apex together with SObject.get() or if I need it for JSON I render the fields as a second JSON string and let the JSON-consuming logic (in my case mostly javascript) iterate over the field-list with lookups to the result-list instead of using the result-list directly.

This pattern helped me to solve all the challenges I encountered so far and my first strong displeasure with the sparse results now changed and I actually even like them. For huge results you'll probably get a more efficient solution with sparse results instead of using full populated results.