SQL查询如何过滤嵌套列Contents为NULL的结果集

问题原因 #

1. SQL 执行顺序中，WHERE 子句优先级高于 SELECT 子句，你直接在主查询 WHERE 中使用 Contents IS NOT NULL 时，数据库还未计算出 Contents 别名列的结果，无法识别该条件，因此筛选不生效。
2. 此处 Contents 列是嵌套 FOR JSON PATH 子查询的返回值，当子查询没有匹配到符合筛选条件的 Content 记录时，就会返回 NULL，导致最终结果中出现 Contents 为空的 Playlist 对象。

解决方案 #

方案1：新增 EXISTS 匹配判断（性能最优） #

在主查询的 WHERE 子句中新增逻辑完全一致的 EXISTS 判断，仅保留存在符合条件内容的 Playlist 记录，不需要改动原有嵌套查询逻辑，执行效率更高：

-- 原有主查询逻辑保持不变
where ( pl.Name LIKE '%' + @SearchInput + '%')
-- 新增如下非空判断逻辑
AND EXISTS (
    select 1
    from dbo.Content c
    inner join dbo.PlayListContents pm on c.Id = pm.ContentId and pm.PlaylistId = pl.Id
    inner join dbo.Files f on c.ContentFileId = f.Id
    inner join dbo.Genres g on c.GenreTypeId = g.Id
    inner join dbo.KeyType kt on c.KeyTypeId = kt.Id
    inner join dbo.TrackType tt on tt.Id = c.TrackTypeId
    WHERE (NOT EXISTS (SELECT b.Bpm FROM @Bpm AS b WHERE b.Bpm IS NOT NULL) OR c.Bpm IN (SELECT * FROM @Bpm))
    AND (NOT EXISTS (SELECT p.Price FROM @Price AS p WHERE p.Price IS NOT NULL) OR c.Price <= ANY(SELECT * FROM @Price ))
    AND (NOT EXISTS (SELECT gt.GenreTypeId FROM @GenreTypeId AS gt WHERE gt.GenreTypeId IS NOT NULL) OR g.Id IN (SELECT * FROM @GenreTypeId))
    AND (NOT EXISTS (SELECT kt.KeyTypeId FROM @KeyTypeId AS kt WHERE kt.KeyTypeId IS NOT NULL) OR kt.Id IN (SELECT * FROM @KeyTypeId))
    AND (NOT EXISTS (SELECT tt.TrackTypeId FROM @TrackTypeId AS tt WHERE tt.TrackTypeId IS NOT NULL) OR tt.Id IN (SELECT * FROM @TrackTypeId))
)
order by newId()
For Json path

方案2：包装为派生表后筛选（代码改动最小） #

如果不想重复编写筛选逻辑，可以把原有主查询包装为派生表/CTE，此时Contents列已经计算完成，再做非空筛选即可生效：

Playlists = (
    SELECT * FROM (
        Select pl.Id as Id
        ,pl.Name as Name
        ,f.[Url] as PrimaryImageUrl
        ,up.Id as MemberId
        ,up.ProfessionalName
        ,up.AvatarUrl
        ,Contents = ( 
            -- 此处保留你原有Contents嵌套子查询的全部逻辑，不需要改动
            for json path
        )
        from dbo.Playlist pl
        inner join dbo.UserProfiles up on pl.UserId = up.UserId
        inner join [dbo].[Files] as f ON pl.[PrimaryImageId] = f.[Id]
        where ( pl.Name LIKE '%' + @SearchInput + '%')
    ) AS PlaylistWithContent
    WHERE PlaylistWithContent.Contents IS NOT NULL
    order by newId()
    For Json path
)

注：数据量较大的场景优先选择方案1，半连接判断不需要返回全量Content数据，执行性能更好。

内容的提问来源于stack exchange，提问作者Chaz Lee