How to use OPENJSON on multiple rows

Question

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.

A few example rows of the temp 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

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 :

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

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.

Answer 1

You'd need to use CROSS APPLY like so:

SELECT id ,
       name ,
       t.Value AS category_id
FROM   #temp
       CROSS APPLY OPENJSON(categories, '$') t;

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