SQL中使用OPENJSON遇笛卡尔积及标识符绑定错误的解决办法

我尝试用以下SQL从JSON中提取对象：

SELECT
    co.contract_number
    , co.objectId id1
    , cbs.id id2
    , co.summary
FROM (
    SELECT
        c.contract_number
        , cb.summary
        , cbo.id objectId
    FROM
        pas.contract C
    CROSS APPLY
        OPENJSON(c.common_body, '$')
            WITH (
                summary NVARCHAR(MAX) '$.summary' AS JSON
              , objects NVARCHAR(MAX) '$.objects' AS JSON
            ) cb
    CROSS APPLY OPENJSON(cb.objects, '$')
    WITH (
            id UNIQUEIDENTIFIER '$.id'
    ) cbo
) co
    CROSS APPLY OPENJSON(co.summary, '$.insuredObjects')
    WITH (
            id UNIQUEIDENTIFIER '$.objectId'
    ) cbs

但出现了笛卡尔积问题：cb.objects里的2个对象和co.summary.insuredObjects里的2个对象组合生成了4行数据，实际返回结果如下：

预期结果是对象一对一匹配的2行数据：

我尝试把最后一个CROSS APPLY替换为LEFT JOIN：

...
    LEFT JOIN OPENJSON(co.summary, '$.insuredObjects')
    WITH (
            id UNIQUEIDENTIFIER '$.objectId'
    ) cbs ON cbs.id = co.objectId

但触发了错误：

The multi-part identifier "co.summary" could not be bound.

请问有没有方法能无错误获取预期结果？

用LEFT JOIN报错的原因是：SQL Server不允许在JOIN关联的表值函数（此处为OPENJSON）中直接引用外层查询的列（co.summary），但APPLY系列操作符支持这种层级关联。

以下是两种可行的解决写法：

写法一：在CROSS APPLY中过滤匹配的ID #

通过子查询在CROSS APPLY中仅返回与当前objectId匹配的insuredObjects条目，避免笛卡尔积：

SELECT
    co.contract_number
    , co.objectId id1
    , cbs.id id2
    , co.summary
FROM (
    SELECT
        c.contract_number
        , cb.summary
        , cbo.id objectId
    FROM
        pas.contract C
    CROSS APPLY
        OPENJSON(c.common_body, '$')
            WITH (
                summary NVARCHAR(MAX) '$.summary' AS JSON
              , objects NVARCHAR(MAX) '$.objects' AS JSON
            ) cb
    CROSS APPLY OPENJSON(cb.objects, '$')
    WITH (
            id UNIQUEIDENTIFIER '$.id'
    ) cbo
) co
CROSS APPLY (
    SELECT id
    FROM OPENJSON(co.summary, '$.insuredObjects')
    WITH (
            id UNIQUEIDENTIFIER '$.objectId'
    )
    WHERE id = co.objectId
) cbs

写法二：调整嵌套层级，直接关联ID #

将两个JSON数组的解析放在同一层级，通过WHERE条件直接匹配ID，逻辑更简洁：

SELECT
    c.contract_number
    , cbo.id id1
    , cbs.id id2
    , cb.summary
FROM pas.contract C
CROSS APPLY OPENJSON(c.common_body, '$')
    WITH (
        summary NVARCHAR(MAX) '$.summary' AS JSON
      , objects NVARCHAR(MAX) '$.objects' AS JSON
    ) cb
-- 解析objects数组
CROSS APPLY OPENJSON(cb.objects, '$')
WITH (
        id UNIQUEIDENTIFIER '$.id'
) cbo
-- 解析insuredObjects数组并匹配ID
CROSS APPLY OPENJSON(cb.summary, '$.insuredObjects')
WITH (
        id UNIQUEIDENTIFIER '$.objectId'
) cbs
WHERE cbo.id = cbs.id

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