Remove empty elements from JSON in SQL Server

Question

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.

Can't you just add "... WHERE Name IS NOT NULL" in SELECT query? — Matt
– Matt, Commented Oct 4, 2018 at 9:49

Hary · Accepted Answer · 2018-10-04 10:12:33Z

5

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

edited Oct 4, 2018 at 10:12

answered Oct 4, 2018 at 9:56

Hary

5,8368 gold badges49 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Haseeb Over a year ago

there is no NULL value in my table but it empty string value in name to be ignored

Haseeb Over a year ago

you are right I can apply condition and it will work, but looking for smart/auto solution instead of adding condition on each field. My actual query contain list of many columns and large number of records and it will slow the performance if i apply case on each field. Yes I can create UDF instead of CASE to cover everything in once place but again UDF slow performance. you answer this is will be solution answer if i don't find any other smart solution.

Matt Over a year ago

Why don't you just make a SQL view with query for all needed fields as @mbharanidharan88 wrote and then select data from view for JSON?

Matt Over a year ago

@mbharanidharan88 answers your initial question. Either edit and expand your question or accept his answer.

critical_error Over a year ago

I know this is an old post, but just in case someone else checks this out, you can replace the CASE statement that tests for an empty string to return null using NULLIF (e.g. NULLIF(Name, '') ).

|

Collectives™ on Stack Overflow

Remove empty elements from JSON in SQL Server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related