Combining STRING_AGG with JSON_ARRAY in T-SQL

Question

Has anyone experienced issues when running a command like this in SSMS?

SELECT 
    STRING_AGG(JSON_ARRAY(COL1, COL2, COL3 NULL ON NULL), ', ') 
FROM 
    {schema}.{table}

In my case this closes the connection to SQL Server with an error:

Msg 109, Level 20, State 0, Line 42
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

• SSMS 19.3
• Microsoft SQL Server 2022 Express Edition (64-bit)
• Windows Server 2016 Datacenter 10.0

To work around this issue I split this activity into 2 steps:

1. Create a temp table with rows created using JSON_ARRAY
2. Combine all rows into array of arrays with STRING_AGG

Answer 1

This bug was fixed in CU1:

2081891 : Fixes an exception that occurs when JSON_ARRAY/JSON_OBJECT return values are used in a parameter in functions that take strings. After you apply this fix, return values of JSON_ARRAY and JSON_OBJECT are made coercible and can be used as string parameters.

So I strongly suggest you upgrade to the latest CU, for this and many other reasons (security, performance, other bug fixes).