CODEWITHSUNDEEP
sqljson
Haseeb
Haseeb

Reputation: 1000

Remove empty elements from JSON in SQL Server

Want to prepare json without columns those are empty.

Here is detail example

MY_TABLE

=================
id ,   Name
=================
1  ,    Ali
2  ,    
3  ,    jhon
=================

SQL STATEMENT FOR JSON

(SELECT [Id],[Name] FROM My_Table)
FOR JSON PATH

SQL RESULT:

[{
    "Id": 1,
    "Name": "Ali"
}, {
    "Id": 2,
    "Name": ""
}, {
    "Id": 3,
    "Name": "Jhon"
}]

But i want to exclude element which has no value like No "Name":"" Element in following result:

[{
    "Id": 1,
    "Name": "Ali"
}, {
    "Id": 2,
}, {
    "Id": 3,
    "Name": "Jhon"
}]

EDITED: Please Note, i can apply CASE or UDF to convert empty values into null and null value may remove from json but it will slow the overall performance with large number of records therefore looking smart solution.

Upvotes: 3

Views: 7100

Answers (1)

Hary
Hary

Reputation: 5818

JSON Auto by default ignore the null fields if INCLUDE_NULL_VALUES not specified explicitly. Check for more info.

To include null values in the JSON output of the FOR JSON clause, specify the INCLUDE_NULL_VALUES option.

If you don't specify the INCLUDE_NULL_VALUES option, the JSON output doesn't include properties for values that are null in the query results.

Also, Sql Fiddle

Ignore Null Values

(SELECT [Id], (CASE WHEN Name = '' THEN NULL ELSE Name END) as Name FROM test)
FOR JSON Auto

Include Null Values

(SELECT [Id],(CASE WHEN Name = '' THEN NULL ELSE Name END) as Name FROM test)
FOR JSON Auto, INCLUDE_NULL_VALUES

Upvotes: 5

Related Questions