SQL Server 2017中的OpenJson：无法绑定多部分标识符

In the [DB_DW].[dbo].DIM_DW_MEETINGS I have a Json Column called Individuals :

在[DB_DW]。[dbo] .DIM_DW_MEETINGS中我有一个名为Individuals的Json列:

[  
   {  
      "Activity_id":1787040082,
      "IndividualID":52528443
   },
   {  
      "Activity_id":1787040082,
      "IndividualID":99312125
   }
]

I now want to execute this query in sql :

我现在想在sql中执行此查询:

SELECT DISTINCT
    *
FROM [DB_DW].dbo.DIM_DW_FACT_ACTIVITIES Fact
INNER JOIN [DB_DW].[dbo].DIM_DW_MEETINGS dimMeetings
    ON Fact.Activity_TECH_KEY = dimMeetings.MEETING_TECH_KEY
LEFT JOIN (SELECT
        *
    FROM OPENJSON(
    (dimMeetings.Individuals)
    ) WITH (
    IndividualID INT '$.IndividualID',
    Activity_id INT '$.Activity_id'
    )) query
    ON query.Activity_id = dimMeetings.Meeting_ID

but I always have this error :

但我总是有这个错误:

The multi-part identifier "dimMeetings.Individuals" could not be bound.

无法绑定多部分标识符“dimMeetings.Individuals”。

1 个解决方案

#1

Use outer apply to the OPENJSON function

使用外部应用于OPENJSON函数

    SELECT DISTINCT
    *
    FROM DIM_DW_FACT_ACTIVITIES Fact
    INNER JOIN DIM_DW_MEETINGS dimMeetings
    ON Fact.Activity_TECH_KEY = dimMeetings.MEETING_TECH_KEY
    OUTER APPLY OPENJSON(
    (dimMeetings.Individuals)
            ) WITH (
    IndividualID INT '$.IndividualID',
    Activity_id INT '$.Activity_id'
    ) query