SQL Server: Including NULL in string concatenation for JSON output

Data and Databases
1

I’m working with SQL Server 2008 and need to create JSON output. My current code replaces NULL values with empty strings, but I want to keep them as NULL in the JSON. Here’s what I’ve tried:

SELECT
  '{"CustomerInfo":' +
  CASE
    WHEN PurchaseDate IS NULL THEN 'null'
    ELSE '"' + CONVERT(varchar(23), PurchaseDate, 120) + '"'
  END +
  '}'
FROM Customers

This still doesn’t work as expected. When PurchaseDate is null, I want the output to be \"PurchaseDate\":null instead of \"PurchaseDate\":\"\".

I’ve also attempted using ISNULL and COALESCE, but no luck. Any ideas on how to handle this? The goal is to have valid JSON with proper NULL representation for my API consumers.

2

hmm, interesting problem! have u considered using a custom function to handle this? something like:

CREATE FUNCTION dbo.ToJsonString(@value SQL_VARIANT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN CASE WHEN @value IS NULL THEN ‘null’
ELSE ‘"’ + CAST(@value AS NVARCHAR(MAX)) + ‘"’
END
END

then u could use it like:
SELECT ‘{“PurchaseDate”:’ + dbo.ToJsonString(PurchaseDate) + ‘}’ FROM Customers

what do u think? would that work for ur case?

3

hey, have u tried using the ISNULL function? it might solve ur problem. something like this:

SELECT ‘“PurchaseDate”:’ + ISNULL(CONVERT(varchar(23), PurchaseDate, 120), ‘null’) AS JsonOutput
FROM Customers

this should give u the correct json output with null values. hope it helps!

4

I’ve encountered a similar issue when working with SQL Server and JSON output. One approach that worked for me is using the FOR JSON PATH clause, which handles NULL values correctly in JSON output. Here’s an example:

SELECT 
    CustomerID,
    PurchaseDate
FROM Customers
FOR JSON PATH, ROOT('CustomerInfo')

This method automatically converts NULL values to JSON null without quotes. It’s available from SQL Server 2016 onwards. If you’re still using SQL Server 2008, you might consider upgrading to take advantage of these built-in JSON features. Alternatively, you could create a custom function to handle the NULL to JSON null conversion, but that’s more complex and error-prone. The FOR JSON approach is cleaner and more maintainable in the long run.