CODEWITHSUNDEEP
sql-serversql-server-2017
Rey
Rey

Reputation: 4002

Remove empty json object when using FOR JSON AUTO syntax

Sorry if the title is a little bit confusing but will try to give a real example to my problem.

So I am trying to use the new Sql Server JSON supporting query syntax.

I do a left join between two tables and of course if the second table does not contain any element I get null values for second table.

select a.Id, b.RefId, b.Name
from table1 a
left join table2 b
on a.Id = b.RefId

The query is simple just to give the idea what I am doing. If I add FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER part at the end of the query I get a nice json string but, if there is no element in table2 matching to any element in table1 then I get and array with an empty object. Something like below:

{
  "Id": 1,
  "b":[{}]
}

This is fine but I want to completely remove the empty object from b element, so, I could have something like this:

{
  "Id": 1,
  "b":[]
}

Is this possible to do in Sql Server instead of writing a custom converter in c# to remove those fake elements ?

Upvotes: 4

Views: 3179

Answers (2)

Zhorov
Zhorov

Reputation: 29983

This is the expected result, as is explained in the documentation:

When you join tables, columns in the first table are generated as properties of the root object. Columns in the second table are generated as properties of a nested object.

What you may try in this case, is to generate a nested FOR JSON PATH subquery in the SELECT statement:

Tables:

CREATE TABLE table1 (
   Id int
)
CREATE TABLE table2 (
   RefId int,
   Name varchar(10)
)
INSERT INTO table1 (Id) VALUES (1), (2)
INSERT INTO table2 (RefId, Name) VALUES (1, N'Name')
INSERT INTO table2 (RefId, Name) VALUES (1, N'Name1')

Statement:

SELECT 
   Id,
   JSON_QUERY(CASE WHEN b IS NULL THEN '[]' ELSE b END) AS b
FROM (
   SELECT 
      a.Id,
      (SELECT RefId, Name FROM table2 WHERE RefId = a.Id FOR JSON PATH) AS b 
   FROM table1 a
) t
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{"Id":1,"b":[{"RefId":1,"Name":"Name"},{"RefId":1,"Name":"Name1"}]},{"Id":2,"b":[]}

Upvotes: 1

sacse
sacse

Reputation: 3744

try the following:

select replace((select a.Id, b.RefId, b.Name
from table1 a
left join table2 b
on a.Id = b.RefId
for json auto, without_array_wrapper), '{}', '')

Upvotes: 2

Related Questions