I'm trying get objects from JSON by this query

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

But here's the problem: double rows (2 objects from cb.objects x 2 objects from co.summary.insuredObjects)

Expected result (objects compare each other 1 to 1):

So I replaced CROSS APPLY by LEFT JOIN

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

But this query causes error:

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

Is there a method to get expected result without errors?

CodePudding user response：

A left join can't refer to itself like that. That's not going to do it unless you run it from a subquery or a CTE. If you're just looking for where the two IDs are equal, the simplest thing would be to add a where statement to get rid of the duplicates.

WHERE co.objectId = cbs.id

Also, I don't know the structure of the JSON, but the nested OPENJSON calls seem unnecessary. You can factor that out.

SELECT c.contract_number, id AS id1, objectId AS id2
  FROM pas.contract
  CROSS APPLY OPENJSON(c.common_body, '$.summary.insuredObjects')
  WITH (
      objectId int '$.objectId' 
  ) cb
  CROSS APPLY
  OPENJSON(c.common_body, '$.objects')
  WITH (
      id int '$.id' 
  ) o
WHERE cb.objectId = o.id

I wouldn't say I'm an OPENJSON expert, so I imagine there may even be a way to get it down to one call. Good luck.