question

JPR-2844 avatar image
0 Votes"
JPR-2844 asked JPR-2844 answered

Storing JSON and returning JSON object

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



sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @JPR-2844

Welcome to microsoft TSQL Q&A forum!

83134-image.png

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.



image.png (10.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JPR-2844 avatar image
0 Votes"
JPR-2844 answered

Hi,
The scalar result I would like to return:
83363-current-scalar-result.png


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".
83381-nvarchar-scalar-result.png




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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered

Hi @JPR-2844

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JPR-2844 avatar image
0 Votes"
JPR-2844 answered

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;


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.