How to get JSON string for each distinct field

I have two tables DbContours:

_|ContourId                           |
1|CDCBC42E-2E86-426E-A9BA-00B8B902A153|
2|FDDD2AC5-172D-4A7B-8106-00D05EC1F41C|

and SimpleLines:

_|SimpleLineId|X1    |Y1    |X2    |Y2    |ContourId                           |
1|Guid        |number|number|number|number|CDCBC42E-2E86-426E-A9BA-00B8B902A153|
2|Guid        |number|number|number|number|CDCBC42E-2E86-426E-A9BA-00B8B902A153|
3|Guid        |number|number|number|number|FDDD2AC5-172D-4A7B-8106-00D05EC1F41C|
4|Guid        |number|number|number|number|FDDD2AC5-172D-4A7B-8106-00D05EC1F41C|

One row in DbContours has multiple corresponding rows in SimpleLines via field ContourId in SimpleLines table. I managed to write query to get all rows in SimpleLines that have ContourId from table DbContours:

SELECT [X1], [Y1], [X2], [Y2] FROM [DDROPDATABASEPROM-2020-10-11-13-13].[dbo].[DbContours] 
INNER JOIN [DDROPDATABASEPROM-2020-10-11-13-13].[dbo].[SimpleLines] 
on [DDROPDATABASEPROM-2020-10-11-13-13].[dbo].[DbContours].ContourId = [DDROPDATABASEPROM-2020-10-11-13-13].[dbo].[SimpleLines].ContourId
order by [DDROPDATABASEPROM-2020-10-11-13-13].[dbo].[DbContours].ContourId

I Want to get json string of all SimpleLines for each distinct ContourID (resulting Select must contain multiple rows of json string based on distinct ContourId) . How to do it?

I think it must be something like this, but for each ContourId and not particular:

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"}';
SELECT JSON_MODIFY(@OriginalJSON, '$.SimpleLines', JSON_QUERY(@Lines)) AS 'Updated JSON';

Each resulting JSON string should look like this:

_| ResultingStrings
1|  {"ContourId":"CDCBC42E-2E86-426E-A9BA-00B8B902A153","SimpleLines":[{"X1":6.62,"Y1":2.11,"X2":6.61,"Y2":2.12},{"X1":5.53,"Y1":3.1,"X2":5.52,"Y2":3.15}]}
2|  {"ContourId":"FDDD2AC5-172D-4A7B-8106-00D05EC1F41C","SimpleLines":[{"X1":6.62,"Y1":2.11,"X2":6.61,"Y2":2.12},{"X1":5.53,"Y1":3.1,"X2":5.52,"Y2":3.15}]}

Answer

If you want …to get json string of all SimpleLines for each distinct ContourID… and the …resulting Select must contain multiple rows of json string based on distinct ContourId…, a possible approach is to generate a JSON output for each ContourId:

Tables:

CREATE TABLE DbContours (
   ContourId uniqueidentifier
)
INSERT INTO DbContours (ContourId)
VALUES
   ('CDCBC42E-2E86-426E-A9BA-00B8B902A153'),
   ('FDDD2AC5-172D-4A7B-8106-00D05EC1F41C')

CREATE TABLE SimpleLines (
   ContourId uniqueidentifier,
   X1 numeric(4, 2),
   Y1 numeric(4, 2),
   X2 numeric(4, 2),
   Y2 numeric(4, 2)
)
INSERT INTO SimpleLines (ContourId, X1, Y1, X2, Y2)
VALUES
   ('CDCBC42E-2E86-426E-A9BA-00B8B902A153', 6.62, 2.11, 6.61, 2.12),
   ('CDCBC42E-2E86-426E-A9BA-00B8B902A153', 5.53, 3.1,  5.52, 3.15),
   ('FDDD2AC5-172D-4A7B-8106-00D05EC1F41C', 6.62, 2.11, 6.61, 2.12),
   ('FDDD2AC5-172D-4A7B-8106-00D05EC1F41C', 5.53, 3.1,  5.52, 3.15)

Statement:

SELECT  
   (
   SELECT 
      dc.ContourId,
      (SELECT X1, Y1, X2, Y2 FROM SimpleLines WHERE ContourId = dc.ContourId FOR JSON AUTO) AS SimpleLines
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
   ) AS ResultingStrings    
FROM DbContours dc

Result:

ResultingStrings
{"ContourId":"CDCBC42E-2E86-426E-A9BA-00B8B902A153","SimpleLines":[{"X1":6.62,"Y1":2.11,"X2":6.61,"Y2":2.12},{"X1":5.53,"Y1":3.10,"X2":5.52,"Y2":3.15}]}
{"ContourId":"FDDD2AC5-172D-4A7B-8106-00D05EC1F41C","SimpleLines":[{"X1":6.62,"Y1":2.11,"X2":6.61,"Y2":2.12},{"X1":5.53,"Y1":3.10,"X2":5.52,"Y2":3.15}]}