sql – How to select rows from one table, for each row of another table and parse them to JSON


I have two tables DbContours and SimpleLines. One row in DbContours has multiple corresponding rows in SimpleLines via field ContourId in SimpleLines table. I Want to get all rows in SimpleLines related to row in DbContours and parse it as JSON string and later insert in some other table field. I can do it for particular ContourId (CDCBC42E-2E86-426E-A9BA-00B8B902A153 in this case). How to do it for all rows in DbContours?

DECLARE @Lines NVARCHAR(MAX) = (SELECT (X1)
      ,(Y1)
      ,(X2)
      ,(Y2)
  FROM (DDROPDATABASEPROM-2020-10-11-13-13).(dbo).(SimpleLines) WHERE ContourId = 'CDCBC42E-2E86-426E-A9BA-00B8B902A153' FOR JSON AUTO);
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100);
SET @OriginalJSON = '{"CalculationProvider":"CalculateWithCSharp"}';
SET @newjson = '{"Ksize":9,"Treshold1":50,"Treshold2":100,"Size1":100,"Size2":250}';
SELECT JSON_MODIFY(@OriginalJSON, '$.SimpleLines', JSON_QUERY(@Lines)) AS 'Updated JSON';