SQL Server解析多对象JSON时CROSS APPLY引发查询结果重复问题

你得到16条重复结果的核心原因是对根节点下的data数组执行了两次独立的CROSS APPLY展开：

• 第一次展开a.data提取distance_data字段，得到4条记录
• 第二次又单独展开a.data提取metadata字段，又得到4条记录
  两次独立展开的结果默认做笛卡尔积计算，最终返回4*4=16条错误结果，和你实际需要的4条记录偏差明显。

不需要对data数组做两次展开，只需要在第一次解析data数组元素时，同时把元素下的metadata、distance_data两个子对象都声明为JSON类型，再分别对这两个同属单条数组元素的子对象做后续解析即可，从根源避免笛卡尔积。

修正后的SQL语句如下：

SELECT
    c.distance_meters, 
    c.steps, 
    CAST(LEFT(e.start_time,10) AS date) AS startDate
FROM  
    OPENJSON(@json)  
    WITH (          
        jType nvarchar(50) N'$.type',
        jUser char(36) N'$.user.user_id',       
        data nvarchar(max) AS JSON
    ) AS a
CROSS APPLY
    -- 仅展开一次data数组，同时提取两个子JSON对象
    OPENJSON(a.data)
    WITH
    (
        distance_data nvarchar(max) AS JSON,
        metadata nvarchar(max) AS JSON
    ) AS b
CROSS APPLY
    OPENJSON(b.distance_data)
    WITH 
    (
        distance_meters float,
        steps int
    ) AS c
CROSS APPLY
    OPENJSON(b.metadata)
    WITH 
    (
        start_time nvarchar(25),    
        end_time nvarchar(25)
    ) AS e
ORDER BY startDate ASC;

可选简化写法：如果不需要兼容过低版本的SQL Server，你甚至可以在解析data数组元素时直接通过JSON路径映射字段，省去后续两层CROSS APPLY，写法更简洁：

SELECT
    distance_meters, 
    steps, 
    CAST(LEFT(start_time,10) AS date) AS startDate
FROM  
    OPENJSON(@json)  
    WITH (          
        jType nvarchar(50) N'$.type',
        jUser char(36) N'$.user.user_id',       
        data nvarchar(max) AS JSON
    ) AS a
CROSS APPLY
    OPENJSON(a.data)
    WITH
    (
        distance_meters float N'$.distance_data.distance_meters',
        steps int N'$.distance_data.steps',
        start_time nvarchar(25) N'$.metadata.start_time',
        end_time nvarchar(25) N'$.metadata.end_time'
    ) AS b
ORDER BY startDate ASC;

执行上述语句后会正确返回4条匹配的记录，和data数组内的有效元素数量一致，不会出现重复数据。

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