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

Question

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

Msg 102, Level 15, State 1, Line 35'JSON_VALUE'附近语法错误。

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

DECLARE @JSON_TABLE VARCHAR(MAX)
SELECT @JSON_TABLE = BulkColumn FROM OPENROWSET(BULK 'C:UsersUserDesktopX tasksupdatedTue, 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

Answer 1

由于每个数组中只有一个对象，您可以删除第三个OPENJSON并将第二个更改为使用直接路径'$.idMembers[0]'

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

如果您省略WITH块，您将获得key/value对，其中key是数组索引，value是对象