如何交叉应用从 SQL 语句中提取的 [“xxxx”，“xxxx”] 等列值-解网

问：

TCARDS。IdMembers 包含像这样的 JSON：[“xxxx”，“xxxx”]，我想将它与我已经拥有的 JSON 交叉应用。第二个生成错误：CROSS APPLY

消息 102，级别 15，状态 1，第 35 行 “JSON_VALUE”附近的语法不正确。

Screenshot1：这是@JSON_TABLE变量的值。它有 [cards] Key whisch 包含我需要的大部分内容，如下面的 SQL 语句所示 Screenshot2：这是 TCards 的结果，但我想进一步分解 idMembers 并交叉应用它。注意：我必须排除其他列

 DECLARE @JSON_TABLE VARCHAR(MAX)
 SELECT @JSON_TABLE = BulkColumn FROM OPENROWSET(BULK 'C:\Users\User\Desktop\X tasks\updated\Tue, 17 Aug 2021 14_40_18 GMT.json', Single_CLob)J
Select 
TCards.id,
TCards.idList,
TCards.idLabels,
TCards.idMembers,
TCards.idChecklists,
TCards.shortUrl,
TCards.customFieldItems,
TMem.Mm1
from OPENJSON(@JSON_TABLE)
    WITH(
        id varchar(500),
        cards Nvarchar(Max) as JSON
    ) AS TABLEA
CROSS APPLY OPENJSON(TABLEA.cards)
    WITH(
        id nVARCHAR(50),
        dateLastActivity nvarchar(50) '$.dateLastActivity',
        idboard nVARCHAR(50) '$.idBoard',
        idChecklists nvarchar(max) '$.idChecklists' as json,
        idMembers nvarchar(max) '$.idMembers' as json,
        idLabels nVarChar(max) '$.idLabels' as json,        
        customFieldItems nvarchar(max) '$.customFieldItems' as json,
        shortUrl nvarchar(50) '$.shortUrl',
        idList nvarchar(50) '$.idList'
    ) as TCards
CROSS APPLY OPENJSON(TCards.IdMembers) 
   WITH(
      Mm1 nvarchar(50) JSON_VALUE(TCards.IdMembers, '$.[0]')
  ) as TMem

JSON SQL 服务器

Select 
TCards.id,
TCards.idList,
TCards.idLabels,
TCards.idMembers,
TCards.idChecklists,
TCards.shortUrl,
TCards.customFieldItems,
TMem.Mm1
from OPENJSON(@JSON_TABLE)
    WITH(
        id varchar(500),
        cards Nvarchar(Max) as JSON
    ) AS TABLEA
CROSS APPLY OPENJSON(TABLEA.cards)
    WITH(
        id nVARCHAR(50),
        dateLastActivity nvarchar(50) '$.dateLastActivity',
        idboard nVARCHAR(50) '$.idBoard',
        idChecklists nvarchar(max) '$.idChecklists' as json,
        idMembers nvarchar(50) '$.idMembers[0]',
        idLabels nVarChar(max) '$.idLabels' as json,        
        customFieldItems nvarchar(max) '$.customFieldItems' as json,
        shortUrl nvarchar(50) '$.shortUrl',
        idList nvarchar(50) '$.idList'
    ) as TCards

如果你真的想打破数组，你会这样做

Select 
TCards.id,
TCards.idList,
TCards.idLabels,
TCards.idMembers,
TCards.idChecklists,
TCards.shortUrl,
TCards.customFieldItems,
TMem.Mm1
from OPENJSON(@JSON_TABLE)
    WITH(
        id varchar(500),
        cards Nvarchar(Max) as JSON
    ) AS TABLEA
CROSS APPLY OPENJSON(TABLEA.cards)
    WITH(
        id nVARCHAR(50),
        dateLastActivity nvarchar(50) '$.dateLastActivity',
        idboard nVARCHAR(50) '$.idBoard',
        idChecklists nvarchar(max) '$.idChecklists' as json,
        idMembers nvarchar(max) '$.idMembers' as json,
        idLabels nVarChar(max) '$.idLabels' as json,        
        customFieldItems nvarchar(max) '$.customFieldItems' as json,
        shortUrl nvarchar(50) '$.shortUrl',
        idList nvarchar(50) '$.idList'
    ) as TCards
CROSS APPLY OPENJSON(TCards.IdMembers) 
   WITH(
      Mm1 nvarchar(50) '$'
  ) as TMem

如果省略块，则会得到一对，其中是数组索引，是对象WITHkey/valuekeyvalue

如何交叉应用从 SQL 语句中提取的 [“xxxx”，“xxxx”] 等列值

How can I cross apply a column value like ["xxxx","xxxx"] extracted from a SQL statement

评论

评论