Re-use T-SQL FOR JSON subquery

Question

Here is a simple example of a nested FOR JSON query:

select 
    u.Id,
    u.Name,
    (select ue.Email
     from UserEmails ue
     where ue.UserId = u.Id
     for json path
    )
from
    Users u
for json path

Is there are way to re-use the UserEmails subquery? For example in a function maybe?

Like:

select 
    u.Id,
    u.Name,
    u.Email,
    dbo.GetUserEmailsJSON(u.Id)
from
    Users u
for json path

I would also like to be able to call dbo.GetUserEmailsJSON() on its own and return the data in JSON format.

Answer 1

I figured it out... The following works

Function:

create function GetUserEmailsJSON
(
    @UserId int
)
returns nvarchar(max)
as
begin
    declare @result nvarchar(max)
    set @result = (
        select *
        from UserEmails ue
        where ue.UserId = @UserId
        for json path, include_null_values
    )

    return @result;
end

And then your query would look like this:

SELECT u.*
      ,json_query(dbo.GetUserEmailsJSON(u.Id)) 'Emails'
  FROM Users u
  for json path, include_null_values

Only thing I don't like about this is calling a scalar-valued function as a subquery - that can be very taxing. Anyone have other suggestions?

Now I need to figure out how to call this function from EF Core and return the JSON into an object... Any suggestions?