For JSON Path Returns Less Number of Rows on Azure SQL

FOR JSON PATH results in SSMS truncated to 2033 characters

The behavior is documented here:

A large result set splits the long JSON string across multiple rows.

By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. The SSMS status bar displays the actual row count.

Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows. For an example of this code in a C# application, see Use FOR JSON output in a C# client app.

Therefore, using FOR JSON to concatenate strings (when the result is longer than 2033 bytes) is not the best idea.

Try using FOR XML instead. For example:

SELECT STUFF((
    SELECT ', '+name FROM sys.columns FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,2,'')

How to retrieve complete a big json from sql server and not chunks in C#?

If you want to return a variable from a stored procedure, instead of a resultset, then use an output parameter:

CREATE PROCEDURE ListState @JSON nvarchar(max) out
AS 
BEGIN 
  SET @JSON = ( SELECT State.Id, State.Nombre, JSON_QUERY((SELECT * FROM Country WHERE Country.Id = State.Pais FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Pais FROM State FOR JSON AUTO); 

END 
GO 

And access the value from a SqlParameter on the client.

Or return the value in a single-row, single-column resultset

CREATE PROCEDURE ListState 
AS 
BEGIN 
  SET @JSON = ( SELECT State.Id, State.Nombre, JSON_QUERY((SELECT * FROM Country WHERE Country.Id = State.Pais FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Pais FROM State FOR JSON AUTO); 
  select @JSON doc;
END 
GO 

And access the value from SqlCommand.ExecuteScalar() or SqlCommand.ExecuteReader() on the client.

Azure Synapse how to CROSS APPLY JSON PATH

I cannot test this in your environment, so this might not work...
You can try one of these:

DECLARE @tblA TABLE(Col1 INT, Col2 VARCHAR(10));
INSERT INTO @tblA(Col1,Col2) VALUES
 (1,'i')
,(2,'ii')
,(3,'iii');

DECLARE @tblB TABLE(A_id INT,B_Col1 VARCHAR(10),B_Col2 VARCHAR(10));
INSERT INTO @tblB(A_id,B_Col1,B_Col2) VALUES
 (1,'b11','b12')
,(1,'b111','b112')
,(2,'b21','b22')
,(2,'b22','b222');

--Pass the column's name behind the CA's name (avoids the nested SELECT)

  SELECT * FROM @tblA as A
    CROSS APPLY (
          SELECT * FROM @tblB as B
          WHERE B.A_id = A.Col1
          FOR JSON PATH
      ) CA(B_JSON);

--Avoid the CA totally by using a scalar sub-select

  SELECT A.Col1
        ,A.Col2
        ,(
          SELECT * FROM @tblB as B
          WHERE B.A_id = A.Col1
          FOR JSON PATH
         ) AS B_JSON
  FROM @tblA as A;

---

Related Topics