Is there any way to return null in JSON_VALUE instead of empty in SQL Server

Question

My sample code is like this:

DECLARE @JsonText varchar(MAX) = '{
    "Test": ""
}'

DECLARE @Name varchar(MAX) = JSON_VALUE(@JsonText, '$.Test') ;

IF (@Name IS NULL)
BEGIN
    SELECT 'Null' AS Result
END
ELSE
BEGIN 
    SELECT 'Empty' AS Result
END

and I got an output:

Result
----------
Empty

My expected output is Null, I have tried with trim and then check string is empty then return null is working fine.

IF (@Result = '') 
    SET @Result = NULL;

But I am looking for any other alternative if available which returns null instead of empty.

If you want the value to be NULL, then define the value as Null in your JSON. Null and "" are not the same value, just like NULL and '' aren't. — Thom A
– Thom A ♦, Commented Nov 25, 2021 at 20:55
Can you explain why you want NULL? NULL signifies unknown but it's not unknown - it's and empty string. — Stu
– Stu, Commented Nov 25, 2021 at 21:01

Thom A · Accepted Answer · 2021-11-25 20:59:19Z

4

A zero length string (in JSON "" and in T-SQL '') and NULL are not the same value. In your JSON you have a zero length string. If you want a NULL value to be returned from your JSON then define the value as Null in your JSON:

DECLARE @JsonText nvarchar(MAX) = N'{
    "Test": null
}';

DECLARE @Name nvarchar(MAX) = JSON_VALUE(@JsonText, '$.Test');
IF (@Name IS NULL)
BEGIN
    SELECT 'Null' AS Result;
END
ELSE
BEGIN 
    SELECT 'Empty' AS Result;
END;

This returns the varchar value 'Null'.

Alternatively, you could use NULLIF to return NULL if your expression returns a zero length string, but I would suggest that really it's the JSON that needs correcting:

SET @Name = NULLIF(JSON_VALUE(@JsonText, '$.Test'),N'');

answered Nov 25, 2021 at 20:59

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Is there any way to return null in JSON_VALUE instead of empty in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related