How to return empty JSON array from SQL Server

Question

I have a database with some random data like:

Book:

    Id uniqueidentifier,
    title nvarchar(255),
    author nvarchar(255),
    genre nvarchar (255),
    deleted datetime

I have many different books in my Book table. When I want to delete a book, I dont really delete it from database, but just set datetime for deleted, when I delete it.

Here is a case that bothers me.

If user tries to look (search) for a book he deleted (which doesn't exist anymore), it needs to return empty array

Here is what I've done, but it doesn't work:

CREATE PROCEDURE dbo.Book_GetById
    @id uniqueidentifier
AS
BEGIN
    DECLARE @_deleted DATETIME
    SET @_deleted = NULL

    SELECT @_deleted  = Deleted 
    FROM Book 
    WHERE Id = @id

    IF (@_deleted IS NOT NULL) 
    BEGIN
        SELECT JSON_QUERY('[]')
        FOR JSON PATH

This is not the entire procedure, just the part where I want to return an empty JSON array by checking if deleted is not null, it means that there is a datetime set for that row - book deleted (if it is null, it is not deleted).

PS1: if there is any other better way than empty array, id like to hear about it too!

PS2: I need to "return" data in my unit testing to see if book is really deleted by checking Assert.IsNotNull(book.Deleted);

When I run my debugger in unit testing and when I step over, my var books says null, which I think isn't really right I guess

Peter B · Accepted Answer · 2017-08-10 10:52:37Z

0

Since JSON is returned as NVARCHAR output, why not do it like this:

IF (@_deleted IS NOT NULL) 
BEGIN
    SELECT '[]'
END ELSE ....

answered Aug 10, 2017 at 10:52

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

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

5 Comments

aiden87 Over a year ago

This is what i get... Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.

aiden87 Over a year ago

it does work like that tho . SELECT JSON_QUERY('[]') arr

Peter B Over a year ago

I never said to use JSON_QUERY or FOR JSON - do it without, you'll get plain text.

aiden87 Over a year ago

i understand now, but in my codebehind im returning json...and if i do that, it gets me this error - Message: Newtonsoft.Json.JsonSerializationException : Cannot deserialize the current JSON array (e.g. [1,2,3]) into type ...Entities.Book because the type requires a JSON object (e.g. {"name":"value"}) to deserialize correctly. To fix this error either change the JSON to a JSON object (e.g. {"name":"value"}) or change the deserialized type to an array or a type that implements a collection interface (e.g. ICollection, IList) like List<T> that can be deserialized from a JSON array.

Peter B Over a year ago

If that problem persists then better to start a new question, it is not something so solve in comments.

Collectives™ on Stack Overflow

How to return empty JSON array from SQL Server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related