Hello EchoLiu,
Just to be clear, the only option I could have with the provided example would be to consider a TABLE function and call it twice (in order to avoid repeated code)
and each of the calls I need to do an explicit FOR JSON?
Example:
CREATE OR ALTER FUNCTION dummy_f(
) RETURNS TABLE AS
RETURN
WITH tmp(someint, somevalue) AS(
SELECT 1,'A' UNION ALL
SELECT 1,'B' UNION ALL
SELECT 1,'C' UNION ALL
SELECT 2,'D' UNION ALL
SELECT 3,'E' UNION ALL
SELECT 3,'F' UNION ALL
SELECT 4,'G' UNION ALL
SELECT 5,'H'
)SELECT x.someint, COUNT(*) AS cnt,
JSON_QUERY((SELECT somevalue FROM tmp WHERE someint = x.someint FOR JSON PATH)) AS items
FROM tmp x
GROUP BY x.someint;
END
GO
SET NOCOUNT ON;
DECLARE @jsonlog TABLE(generatedfile nvarchar(MAX));
-- Keep log of generated file
INSERT INTO @jsonlog(generatedfile) VALUES((SELECT * FROM dbo.dummy_f()
FOR JSON PATH));
SELECT * FROM dbo.dummy_f()FOR JSON PATH;