Data not showing if null


I am calling a stored procedure and have noticed if a value is null the field is not returned when querying the entity.

You can see below the AssignedTo field is null when calling the stored proc
However when querying the entity, the field only appears if there is a value.

Is this behaviour expected?

HI Adam - I believe this is expected behaviour and entirely normal for a json / document database to hold values with different “columns” representing the reality of the underlying data. There are a couple of ways to deal with this but in principle I don’t think it should create an issue for you. Off the top of my head you can “fix” this on the SQL Server side with the “isnull()” function which will return a placeholder of your choice if the field is null (e.g. select id , ISNULL(AssignedTo, " ") or whatever is appropriate.

You can do hte same on hte SQLite side with your select statement so instead of using the default ‘$.AssignedTo’ , you can do something like the following

select json_extract(data , ‘$.jobid’) as jobid
, json_extract(data , ‘$.title’) as title
,ifnull(json_extract(data , ‘$.AssignedTo’), ’ ') as AssignedTo

Let me know if either of those work for you