subquery results appends to all main query selected field

Question

I'm writing a stored procedure to get list of IOT parameter data under a specific machine and the result of stored procedure should have a specific JSON format. the problem is, when I tried to add a parameter data under another machine I found out the subquery result append all its results under all machines. I want to Alter the query so each machine should have just its parameter data under it and not changing the JSON format.

This is the Stored procedure:

declare @jsonTwo nvarchar(max)=(
Select JSON_QUERY((
select CAST((
select 
MAE.MachineName as MachineName
, 
(select IOTR.MachineCode, IOTP.IotParameterName, IOTR.CreatedAt, datename(WEEKDAY, IOTR.CreatedAt) as FilterRange, 
avg(IOTP.IotParameterValue) as ParameterValue,MP.UpperControlLimit , MP.LowerControlLimit
from IOTMachineParameters IOTP 
inner join IOTMachineReadings IOTR ON IOTP.IotMachineID = IOTR.Id
inner join MachineAndEquipments MAE on MAE.MachineCode = IOTR.MachineCode
inner join MachineParameters MP on IOTP.IotParameterName = MP.ParamterName
where 
MP.ParameterType = 'PARAMETERIZED' 
and IotP.IsChecked = 1 
and IOTR.CompanyCode = 'DA-1663079927040'
and MAE.MachineCode = IOTR.MachineCode
and IOTP.IotMachineID = IOTR.Id
and IOTR.CreatedAt >= '2022-09-01' and IOTR.CreatedAt <= '2022-11-16 10:11:00.0000000'
group by IOTR.MachineCode,IOTP.IotParameterName,IOTR.CreatedAt,datename(WEEKDAY, IOTR.CreatedAt),MAE.MachineName,MP.LowerControlLimit,MP.UpperControlLimit
 for json path) as MachineReadings
from  MachineAndEquipments MAE inner join IOTMachineReadings IOTR ON MAE.MachineCode = IOTR.MachineCode
inner join IOTMachineParameters IOTP ON IOTP.IotMachineID = IOTR.Id
where MAE.CompanyId = 'DA-1663079927040'
and IOTP.IotMachineID = IOTR.Id
group by MAE.MachineName
 for json path ,Include_null_values)as nvarchar(max))as part1 for json path, without_array_wrapper)));
select @jsonTwo as data

when i run the MachineReadings subquery it returns all the records reults of MachineReadings subquery The tables are: Tables of the query so is there a way to like filter out the subquery results based on the outer selection of Machine Name.

I expected each object, has Machine name and a list of IOT parameter reading data under this machine name only. Instead I found that each Machine name has the same exact list of IOT parameter reading data

Targeted JSON format:

{
   "part1":[
      {
         "MachineName":"Machine X",
         "MachineReadings":[
            {
               "MachineCode":"Machine-012",
               "MachineName":"Machine X",
               "IotParameterName":"t",
               "CreatedAt":"2022-11-14T11:11:42",
               "FilterRange":"Monday",
               "ParameterValue":20.000000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            }
         ]
      },
      {
         "MachineName":"Machine Y",
         "MachineReadings":[            
            {
               "MachineCode":"Machine-789",
               "MachineName":"Machine Y",
               "IotParameterName":"a test",
               "CreatedAt":"2022-11-16T10:11:00",
               "FilterRange":"Wednesday",
               "ParameterValue":3.000000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            },
            {
               "MachineCode":"Machine-789",
               "MachineName":"Machine Y",
               "IotParameterName":"new parameter",
               "CreatedAt":"2022-11-15T10:09:51",
               "FilterRange":"Tuesday",
               "ParameterValue":13.500000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            }
         ]
      }
   ]
}

This is The Resulted JSON Format:

{
   "part1":[
      {
         "MachineName":"rtyy",
         "MachineCode":"Machine-012",
         "MachineReadings":[
            {
               "MachineCode":"Machine-012",
               "MachineName":"rtyy",
               "IotParameterName":"t",
               "CreatedAt":"2022-11-14T11:11:42",
               "FilterRange":"Monday",
               "ParameterValue":20.000000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            },
            {
               "MachineCode":"Machine-789",
               "MachineName":"the other 789",
               "IotParameterName":"a test",
               "CreatedAt":"2022-11-16T10:11:00",
               "FilterRange":"Wednesday",
               "ParameterValue":3.000000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            },
            {
               "MachineCode":"Machine-789",
               "MachineName":"the other 789",
               "IotParameterName":"new parameter",
               "CreatedAt":"2022-11-15T10:09:51",
               "FilterRange":"Tuesday",
               "ParameterValue":13.500000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            }
         ]
      },
      {
         "MachineName":"the other 789",
         "MachineCode":"Machine-789",
         "MachineReadings":[
            {
               "MachineCode":"Machine-012",
               "MachineName":"rtyy",
               "IotParameterName":"t",
               "CreatedAt":"2022-11-14T11:11:42",
               "FilterRange":"Monday",
               "ParameterValue":20.000000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            },
            {
               "MachineCode":"Machine-789",
               "MachineName":"the other 789",
               "IotParameterName":"a test",
               "CreatedAt":"2022-11-16T10:11:00",
               "FilterRange":"Wednesday",
               "ParameterValue":3.000000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            },
            {
               "MachineCode":"Machine-789",
               "MachineName":"the other 789",
               "IotParameterName":"new parameter",
               "CreatedAt":"2022-11-15T10:09:51",
               "FilterRange":"Tuesday",
               "ParameterValue":13.500000,
               "UpperControlLimit":0.00,
               "LowerControlLimit":0.00
            }
         ]
      }
   ]
}

Answer 1

The issue could be there's no relationship between the correlated subquery MachineReadings and tables in outer query FROM clause. Thus, the result of MachineReadings is applied to every row returned by outer query.

Formatted your code below for easy reference:

declare @jsonTwo nvarchar(max)=(
Select
    JSON_QUERY((
    select
        CAST((
        select
            MAE.MachineName as MachineName
,
            (
            select
                IOTR.MachineCode,
                IOTP.IotParameterName,
                IOTR.CreatedAt,
                datename(WEEKDAY, IOTR.CreatedAt) as FilterRange,
                avg(IOTP.IotParameterValue) as ParameterValue,
                MP.UpperControlLimit ,
                MP.LowerControlLimit
            from
                IOTMachineParameters IOTP
            inner join IOTMachineReadings IOTR ON
                IOTP.IotMachineID = IOTR.Id
            inner join MachineAndEquipments MAE on
                MAE.MachineCode = IOTR.MachineCode
            inner join MachineParameters MP on
                IOTP.IotParameterName = MP.ParamterName
            where
                MP.ParameterType = 'PARAMETERIZED'
                and IotP.IsChecked = 1
                and IOTR.CompanyCode = 'DA-1663079927040'
                and MAE.MachineCode = IOTR.MachineCode
                and IOTP.IotMachineID = IOTR.Id
                and IOTR.CreatedAt >= '2022-09-01'
                and IOTR.CreatedAt <= '2022-11-16 10:11:00.0000000'
            group by
                IOTR.MachineCode,
                IOTP.IotParameterName,
                IOTR.CreatedAt,
                datename(WEEKDAY, IOTR.CreatedAt),
                MAE.MachineName,
                MP.LowerControlLimit,
                MP.UpperControlLimit
 for json path) as MachineReadings
        from
            MachineAndEquipments MAE
        inner join IOTMachineReadings IOTR ON
            MAE.MachineCode = IOTR.MachineCode
        inner join IOTMachineParameters IOTP ON
            IOTP.IotMachineID = IOTR.Id
        where
            MAE.CompanyId = 'DA-1663079927040'
            and IOTP.IotMachineID = IOTR.Id
        group by
            MAE.MachineName
 for json path ,
            Include_null_values)as nvarchar(max))as part1 for json path,
        without_array_wrapper)));
select @jsonTwo as data