SQL Server FOR JSON PATH, Exclude specific field if null

Question

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.

then your json format is wrong and have bad consequences – A Farmanbar Oct 01 '20 at 20:39 — A Farmanbar, Oct 01 '20 at 20:39

critical_error · Accepted Answer · 2020-10-01T22:28:54.783

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": ""
}]

Zhorov · Answer 2 · 2020-10-02T11:48:55.927

Update:

The original question was edited and I don't think that you can generate the expected ouptut using a single FOR JSON and INCLUDE_NULL_VALUES, because now the table has more than one column with NULL values (OtherField in the example).

As a possible solution you may try a mixed approach (using FOR JSON and STRING_AGG()) to build the final JSON output and keep the NULL values for all columns, except Phones:

CREATE TABLE Data (
   Id int, 
   Name varchar(100), 
   Phone varchar(100),
   OtherField varchar(1)
);
INSERT INTO Data (Id, Name, Phone, OtherField) 
VALUES
    (1, 'ABC', '12344', NULL), 
    (2, 'ABC', NULL, NULL),
    (3, 'ABC', NULL, NULL)

Statement:

SELECT CONCAT(
   '[',
   (
   SELECT STRING_AGG(j.Json, ',')
   FROM Data d
   CROSS APPLY (
      SELECT CASE
         WHEN Phone IS NOT NULL THEN (
            SELECT Id, Name, (SELECT Phone AS Home FOR JSON PATH) AS Phone, OtherField 
            FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
         )
         ELSE (
            SELECT Id, Name, OtherField
            FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
         )
      END
   ) j (Json)
   ),
   ']'
)

Result:

[
   {"Id":1,"Name":"ABC","Phone":[{"Home":"12344"}],"OtherField":null},
   {"Id":2,"Name":"ABC","OtherField":null},
   {"Id":3,"Name":"ABC","OtherField":null}
]

Original answer:

You may try the following statement:

Table:

CREATE TABLE Data (
   Id int, 
   Name varchar(100), 
   Phone varchar(100) 
);
INSERT INTO Data (Id, Name, Phone) 
VALUES
    (1, 'ABC', '12344'), 
    (2, 'ABC', NULL )

Statement:

SELECT 
   Id, 
   Name, 
   JSON_QUERY(CASE WHEN Phone IS NOT NULL THEN (SELECT Phone AS Home FOR JSON PATH) END) AS Phone
FROM Data
FOR JSON PATH

Result:

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

it works but I want to remove phone from 2nd object, like this [ {"Id":1,"Name":"ABC","Phone":[{"Home":"12344"}],"OtherField":null}, {"Id":2,"Name":"ABC","OtherField":null} ] — Ali, Oct 01 '20 at 21:04

SQL Server FOR JSON PATH, Exclude specific field if null

2 Answers2