存储过程JSON输出异常：子查询结果错误关联所有机器

编写存储过程获取特定设备下的IoT参数数据并输出指定JSON格式，但添加另一台设备后，子查询结果被附加到所有设备下，需要修改查询让每个设备仅显示自身的IoT参数数据，且不改变原有JSON格式。

原存储过程代码 #

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

预期JSON格式 #

{
   "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
            }
         ]
      }
   ]
}

实际输出JSON格式 #

{
   "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
            }
         ]
      }
   ]
}

解决方案 #

问题核心是子查询未与外层查询的设备建立关联，导致返回所有设备的数据。修改步骤如下：

1. 给外层的MachineAndEquipments表起别名MAE_Outer，用于子查询关联
2. 删除子查询中多余的inner join MachineAndEquipments MAE，避免重复关联
3. 在子查询的WHERE条件中添加MAE_Outer.MachineCode = IOTR.MachineCode，确保子查询仅返回当前外层设备的读数

修改后的存储过程代码：

declare @jsonTwo nvarchar(max)=(
Select JSON_QUERY((
select CAST((
select 
MAE_Outer.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 MachineParameters MP on IOTP.IotParameterName = MP.ParamterName
where 
MP.ParameterType = 'PARAMETERIZED' 
and IotP.IsChecked = 1 
and IOTR.CompanyCode = 'DA-1663079927040'
and IOTP.IotMachineID = IOTR.Id
and IOTR.CreatedAt >= '2022-09-01' and IOTR.CreatedAt <= '2022-11-16 10:11:00.0000000'
-- 关联外层设备，确保仅返回当前设备的数据
and MAE_Outer.MachineCode = IOTR.MachineCode
group by IOTR.MachineCode,IOTP.IotParameterName,IOTR.CreatedAt,datename(WEEKDAY, IOTR.CreatedAt),MP.LowerControlLimit,MP.UpperControlLimit
 for json path) as MachineReadings
from  MachineAndEquipments MAE_Outer 
inner join IOTMachineReadings IOTR ON MAE_Outer.MachineCode = IOTR.MachineCode
inner join IOTMachineParameters IOTP ON IOTP.IotMachineID = IOTR.Id
where MAE_Outer.CompanyId = 'DA-1663079927040'
and IOTP.IotMachineID = IOTR.Id
group by MAE_Outer.MachineName
 for json path ,Include_null_values)as nvarchar(max))as part1 for json path, without_array_wrapper)));
select @jsonTwo as data

说明 #

• 通过MAE_Outer.MachineCode = IOTR.MachineCode建立外层与子查询的关联，保证每个设备只获取自身的IoT读数
• 移除子查询中重复的MachineAndEquipments关联，减少不必要的表连接，提升查询效率
• 保留原有JSON结构，仅过滤数据关联逻辑

内容的提问来源于stack exchange，提问作者dode