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 a 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);zDECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100);
SET @OriginalJSON = '{"CalculationProvider":"CalculateWithCSharp"}';
SELECT JSON_MODIFY(@OriginalJSON, '$.SimpleLines', JSON_QUERY(@Lines)) AS 'Updated JSON';

Answer

Start by separating out the tasks required. The first thing you need to do is get your query figured out. Since you say the tables are related, you should use a JOIN to get all the rows which match your criteria.

Get that working in your query, then you can run it through the JSON functions you need.

Finally, if you want to insert those into a new table, you can use a form of CTAS – Create Table As Select or with a CTE Common Table Expression.