Search code examples
sqljsonsql-servert-sqlopen-json

SQL: The multi-part identifier could not be bound with OPENJSON


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)

contract_number id1 id2
2200001459 1 1
2200001459 1 2
2200001459 2 1
2200001459 2 2

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

contract_number id1 id2
2200001459 1 1
2200001459 2 2

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?


Solution

  • 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.