Storing JSON and returning JSON object

Question

Hello,
Is it possible to return a json object without repeating code?

I have a procedure that the scalar value always returns a json object.
If I save the json to a nvarchar variable, I lose that "reference". Example "JSON_F52E2B61-18A1-11d1-B105-00805F49916B"

The scalar result seems that needs to be an "FOR JSON" syntax in order to work with the app that calls that procedure.

An example would be something like this:

SET NOCOUNT ON;
DECLARE @json nvarchar(MAX);
DECLARE @data TABLE(someint int, somevalue varchar(80));

INSERT INTO @data(someint, somevalue)
VALUES(1,'A'), (1,'B'), (1,'C'), (2,'D'), (3,'E'), (3,'F'), (4,'G'), (5,'H')

DECLARE @jsonlog TABLE(generatedfile nvarchar(MAX));

SET @json = (
    SELECT  x.someint, COUNT(*) AS cnt,
            JSON_QUERY((SELECT somevalue FROM @data WHERE someint = x.someint FOR JSON PATH)) AS items
    FROM @data x
    GROUP BY x.someint
    FOR JSON PATH
);

-- Keep log of generated file
INSERT INTO @jsonlog(generatedfile) VALUES(@json);

-- return JSON object to app-client (stored procedure)
SELECT @json AS ' ' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- something like this? the json is not the same of course
--SELECT JSON_QUERY(@json) -- does not work

Answer 1

Hi @JPR ，

No, process variables can only be used in this process and must be declared again when they are used in the next process. Unless you give up the use of variables.

Regards
Echo

Answer 2

Is it possible to return a json object without repeating code?

SQL Server is a relational database and returns structured data, but not objects.

Answer 3

Hi @JPR ，

Welcome to microsoft TSQL Q&A forum!

I don't understand what you mean, could you explain it?

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi,
The scalar result I would like to return:

But with changes (saving that json query to a variable in order to save that before exposing the scalar result) I lose that "json object".

I could save and return the same json if I duplicate the FOR JSON code, my question is:
is there is a way to avoid duplicating the code?

 SET NOCOUNT ON;  
 DECLARE @json nvarchar(MAX);  
 DECLARE @data TABLE(someint int, somevalue varchar(80));  
      
 INSERT INTO @data(someint, somevalue)  
 VALUES(1,'A'), (1,'B'), (1,'C'), (2,'D'), (3,'E'), (3,'F'), (4,'G'), (5,'H')  
      
 DECLARE @jsonlog TABLE(generatedfile nvarchar(MAX));  
      
 SET @json = (  
     SELECT    x.someint, COUNT(*) AS cnt,  
             JSON_QUERY((SELECT somevalue FROM @data WHERE someint = x.someint FOR JSON PATH)) AS items  
     FROM @data x  
     GROUP BY x.someint  
     FOR JSON PATH  
 );  
      
 -- Keep log of generated file  
 INSERT INTO @jsonlog(generatedfile) VALUES(@json);  
      
 -- return JSON object to app-client (stored procedure)  
 --SELECT @json AS ' ' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- something like this? the json is not the same of course  
 --SELECT @json -- does not work  
  
SELECT    x.someint, COUNT(*) AS cnt,  
            JSON_QUERY((SELECT somevalue FROM @data WHERE someint = x.someint FOR JSON PATH)) AS items  
FROM @data x  
GROUP BY x.someint  
FOR JSON PATH -- repeating the code, but this is the expected scalar result for the app  

Answer 5

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;