sqljsonsql-serversql-server-json

SQL Server FOR JSON PATH, Exclude specific field if null


I have table with data like this

Id  |  Name   | Phone  | OtherField 
----+---------+--------+-----------
 1  | ABC     | 12344  | NULL
 2  | XYZ     | NULL   | NULL

I want a SQL query to transform it like this

[
    {
      "ID":1,
      "Name":"ABC",
      "Phone":[
               {"Home":"12344"}
             ],
       "OtherFields":NULL
     },
    {
     "ID":1,
     "Name":"ABC",
     "OtherFields":NULL
    }
]

I know about INCLUDE_NULL_VALUES it includes all the empty field. I want to include all other fields except Phone.


Solution

  • I have edited my answer as you have changed your original request.

    I don't believe you can have it both ways, keeping some NULLs and not others. The best way I can think of at the moment is to use ISNULL on columns you must keep.

    For example:

    DECLARE @Table TABLE ( Id INT, Name VARCHAR(10), Phone VARCHAR(10), OtherField VARCHAR(10) );
    INSERT INTO @Table ( Id, Name, Phone ) VALUES
        ( 1, 'ABC', '12344' ), ( 2, 'XYZ', NULL );
    
    SELECT 
        Id, Name, 
        JSON_QUERY ( CASE
            WHEN t.Phone IS NOT NULL THEN x.Phone
            ELSE NULL
        END ) AS Phone,
        ISNULL( OtherField, '' ) AS OtherFields
    FROM @Table t
    CROSS APPLY (
        SELECT ( SELECT Phone AS Home FOR JSON PATH ) AS Phone
    ) x
        FOR JSON PATH;
    

    Returns

    [{
        "Id": 1,
        "Name": "ABC",
        "Phone": [{
            "Home": "12344"
        }],
        "OtherFields": ""
    }, {
        "Id": 2,
        "Name": "XYZ",
        "OtherFields": ""
    }]