Why are null strings in my database not returned with returnFormat = JSON

Question

{
MESSAGE = LoginWelcomeBackMessage;
SUCCESS = 1;
USER =     {
    AcceptAllFriendRequests = 0;
    CreateDate = "June, 07 2010 00:00:00";
    DayOfBirth = "April, 24 1974 00:00:00";
    UserName = "Erik Madsen";
};
}

My CFC defines all the columns in my database table, but when a column is NULL, the field isn't returned as a property in the JSON? The example above should have PersonalInfo = ""; Is there a way to force it to return an empty string?

Answer 1

An empty string in a database column is not the same as a NULL in a column.

Have a look at this article: http://www.bennadel.com/blog/1654-Learning-ColdFusion-9-IsNull-And-Working-With-NULL-Values.htm

Typically, you may want to wrap these at the database (in a view or a computed column) with ISNULL(col, '') or COALESCE(col, '') or handle these after extraction from the database as the article discusses. Or don't even allow NULLs in the database, require empty strings instead.

Answer 2

Simply leaving the property out is probably closer to null than an empty string in most circumstances. if you want an empty string you should return an empty string. how a null value is treated depends on your serializer.

In javascript, the official implementation handles it quite fine:

var a = { b: null };
console.log( JSON.stringify(a) ); // logs '{"b":null}'