Query ReturnType=Struct / Handling Nulls

So for a couple of years now I have been converting query results into a nested struct, using one of the row values as the key. I was pleasantly surprised a few weeks ago when I discovered that Lucee lets you do this by default, by using returnType=struct and columnKey equal to the field you want to use as the key.

However the issue I have with this is that when it creates the structs, rather than converting any nulls to empty strings like the regular query struct, Lucee will instead keep the values as actual nulls. To be clear I do understand that keeping nulls can be helpful, but for us it is problematic.

Yes, you can use isnull(, ‘’) to get the same effect, but this tends to make the query look horribly messy, and gets complicated if the type isn’t a char field of some kind, so it would be nice if Lucee gave us a way to override the default behavior, and have returnType=Struct use empty strings for null values as well.

So my suggestion is fairly straightforward. Have an additional boolean attribute, maybe named something like keepNulls.
If set to true, then returnType=Struct can use nulls in the resulting struct (default behavior).
If false, it uses empty strings in place of nulls.

Something else that might be worth considering to add some additional flexibility is to override the default null value, regardless of whether the returntype=Query, struct, array, whatever.
So rather than always using empty string for nulls, the developer can specify what the text should be when the value is null.
So have an attribute called nullValue. If it’s not defined, then the query can use the default value for nulls (i.e. empty string). If it’s defined, then it’s value is used instead.

If you wanted to make things super duper flexible, then instead of the keepNulls flag attribute I mentioned earlier, instead have an attribute called nullType, which can be set to null, int, string, etc.

Honestly, if full null support is turned off I’d consider this a bug. Since running the equivalent of myQry.myNullColumn gives you an empty string when full null support is off. This is probably more of an oversight in the implementation. People who expect to get actual nulls back from queries are presumably setting Lucee’s full null support setting to “on”.

I’d put in a ticket suggesting that the query’s array of structs feature obey the null support setting and see what the dev team says.

After the lucee version by the ticket LDEV-2236 when Null Support is Partial support, using query with returntype=struct/array lucee places the empty string for nulls. With full null support lucee keeps the null values as null.
@jacobfw which lucee version do you have?

1 Like

Yep, looks like that’s it. We’re on Lucee

Thanks guys!