Do not include fixed value in JSON SQL

Question

I have to make JSON from the table. Problem is, that I have to show NULL values, but hide all fixed constant values.

For example, I have dataset #table. In the JSON output I want to show all values, where Value != 0. Deleting row (select Value from #table where cn = 'c') as 'c' isn't an option. How can I do that?

create table #table (
   Value int,
   cn nvarchar(1)
)

insert into #table
values
(null, 'a'),
(null, 'b'),
(0, 'c'),
(3, 'd'),
(3, 'f')

select
    (select Value from #table where cn = 'a')  as 'a',
    (select Value from #table where cn = 'b') as 'b', 
    (select Value from #table where cn = 'c') as 'c', 
    (select Value from #table where cn = 'd') as 'd', 
    (select Value from #table where cn = 'f') as 'f'
FOR JSON PATH, INCLUDE_NULL_VALUES 

Expected output would be:

[{"a":null,"b":null,"d":3,"f":3}]

Answer 1

You can do it with a dynamic sql. I use conditional aggregation to pivot the table.

declare @cmd nvarchar(max) = 'select ' + stuff(
    (select  ', max(case cn when ''' + cn + ''' then value end) as ''' + cn +''''
     from #table
     where value <> 0 or value is null
     for xml path(''))
    , 1, 1, '') + ' from #table FOR JSON PATH, INCLUDE_NULL_VALUES';

 --select @cmd;

 exec (@cmd);

Another option builds on the type of the value column (int), so 0.5 is impossible as a column value. Cast the original values to decimal, then replace impossible values with null and remove decimal point and 0 after it.

select replace(replace(
  (select
    max(case cn when 'a' then value end) 'a',
    max(case cn when 'b' then value end) 'b',
    max(case cn when 'c' then value end) 'c',
    max(case cn when 'd' then value end) 'd',
    max(case cn when 'f' then value end) 'f'
  from( 
   select coalesce(cast(value as decimal), 0.5) value, cn 
   from #table
   where value <> 0 or value is null) t
   FOR JSON PATH)
,'0.5', 'null'), '.0', '');