如何在多行上使用 OPENJSON [英] How to use OPENJSON on multiple rows
问题描述
我有一个包含多行的临时表,每一行都有一个名为 Categories
的列;它包含一个非常简单的 ids
json 数组,用于不同表中的类别.
I have a temp table with multiple rows in it and each row has a column called Categories
; which contains a very simple json array of ids
for categories in a different table.
临时表的几个示例行:
Id Name Categories
---------------------------------------------------------------------------------------------
'539f7e28-143e-41bb-8814-a7b93b846007' Test 1 ["category1Id", "category2Id", "category3Id"]
'f29e2ecf-6e37-4aa9-aa56-4a351d298bfc' Test 2 ["category1Id", "category2Id"]
'34e41a0a-ad92-4cd7-bf5c-8df6bfd6ed5c' Test 3 NULL
现在我想做的是从临时表中的所有行中选择所有类别 ID.
Now what I would like to do is to select all of the category ids from all of the rows in the temp table.
我所拥有的是以下内容,但它无法正常工作,因为它给了我以下错误:
What I have is the following and it's not working as it's giving me the error of :
子查询返回了 1 个以上的值.当子查询跟在 =、!=、<、<=、>、>= 或当子查询用作表达式时,这是不允许的.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SELECT
c.Id
,c.[Name]
,c.Color
FROM
dbo.Category as c
WHERE
c.Id in (SELECT [value] FROM OPENJSON((SELECT Categories FROM #TempTable)))
and c.IsDeleted = 0
我认为失败是有道理的,因为我选择了多行并且需要解析每行各自的类别 ID json.我只是不知道该怎么做/改变才能给我想要的结果.在此先感谢您的帮助.
Which I guess it makes sense that's failing on that because I'm selecting multiple rows and needing to parse each row's respective category ids json. I'm just not sure what to do/change to give me the results that I want. Thank you in advance for any help.
推荐答案
你需要像这样使用 CROSS APPLY:
You'd need to use CROSS APPLY like so:
SELECT id ,
name ,
t.Value AS category_id
FROM #temp
CROSS APPLY OPENJSON(categories, '$') t;
然后,您可以使用 category_id
列加入您的 Categories
表,如下所示:
And then, you can JOIN to your Categories
table using the category_id
column, something like this:
SELECT id ,
name ,
t.Value AS category_id,
c.*
FROM #temp
CROSS APPLY OPENJSON(categories, '$') t
LEFT JOIN Categories c ON c.Id = t.Value
这篇关于如何在多行上使用 OPENJSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!