Logic App Execute SQL TO JSON automatically chunks output

Question

The logic app I am working with is intended to quickly update a json file, which is based on a SQL Server table (1000 rows, 6 columns).

The SQL statement resembles this:

SELECT ID, NAME, FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLENAME FOR JSON PATH;

There are ~1000 rows in the table, with little variance or changes.

When I run this SQL in SSMS or locally, my output is a single row / consolidated json output; when I run the same SQL via the Logic App, it batches the output into groups of 10 json rows.

screenshot of output from stored proc / execute sql

If I use a stored procedure with NO COUNT ON, the same behavior results.

Does anyone know a method to force the Execute SQL task in logic apps NOT to chunk / batch the return into different resultsets?

Answer 1

I've since learned that the Execute SQL automatically casts its output to Json.

To fix this, I changed my SQL to remove the FOR JSON PATH, and used ResultSet.Table1 as the source for a Compose Task. This wraps the array with the Json-specific square brackets, and now output is as expected.