I have a sql query that returns a list of objects as json similar to this:
The query in question:
SELECT TotalRows
,JSON_QUERY(
(SELECT a.a_AlbumId AS Id
,CA.Id as CampaignId
,a.a_Name AS Name
,a.a_ReleaseDate as ReleaseDate
,s.TrackCount AS NumSongs
,art.art_Json AS Artists
,i.i_Json AS SourceImage
FROM Albums_CTE a
INNER JOIN SongCount_CTE s ON a.a_AlbumId = s.s_AlbumId
INNER JOIN Campaigns CA ON CA.AlbumId = a.a_AlbumId
LEFT JOIN Artists_CTE art ON a.a_AlbumId = art.art_AlbumId
LEFT JOIN ImageSources_CTE i ON a.a_AlbumId = i.i_AlbumId
ORDER BY a.a_Name
OFFSET ISNULL(@pageSize * @pageNumber, 0) ROWS
FETCH NEXT ISNULL(@pageSize, TotalRows) ROWS ONLY
FOR JSON PATH)
) AS Albums
FROM AlbumsCount_CTE
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
When I run this sql querying my database directly I get exactly what I expect:
{TotalRows: 5, Albums: [{...}, {...}, ...]}
*omitting the actual json data for brevity
Alternatively, When this code is running on the deployed azure service it returns
{TotalRows: 5, Albums: null}
I know that this was working at some point but I am not sure exactly when it broke. I anticipate it is related to some nuget package update.
Thanks for any insight into this problem.