Azure Synapse How To Cross Apply Json Path

I'd like to dynamically pull rows of data based on a few fields from another table and summarize it as JSON when joining it in as a single row. Here's a small example to illustrate

Solution 1:

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

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

DECLARE@tblBTABLE(A_id INT,B_Col1 VARCHAR(10),B_Col2 VARCHAR(10));
INSERTINTO@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 @tblAasACROSSAPPLY (
          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

SELECTA.Col1
        ,A.Col2
        ,(
          SELECT * FROM @tblB as B
          WHERE B.A_id = A.Col1
          FOR JSON PATH
         ) ASB_JSONFROM @tblAasA;