Use STUFF() on a result set coming from OPENJSON

Question

I have a ClassTable as shown below with a Languages column in which the data is in JSON:

ID	Title	Languages
1	Class1	[{"ID": 1, "Name": "English"},{"ID": 2, "Name": "Hindi"}]
2	Class2	[{"ID": 1, "Name": "Marathi"},{"ID": 2, "Name": "Telugu"}]

and a Master table of Languages as

ID	Name
1	English
2	Hindi
3	Marathi
4	Telugu

I need output as below

ID	Title	LanguageIDs
1	TestTitle1	1,2

I am trying to achieve this with OPENJSON to get data from JSON and then I am applying STUFF() to that data so that I would get comma separated LanguageIDs

This is the query, I have written but I am not getting the expected output

SELECT 
    A.ID,
    A.Title,
    LanguageIDs = STUFF ((SELECT CONCAT(',',A.ID)
                          FROM Master.Languages
                          WHERE ID IN (A.LanguageID)
                          FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
FROM
    (SELECT 
         X.ID,
         X.Title,
         X.CreatedOn,
         B.ID as LanguageID
     FROM 
         ClassTable X
     CROSS APPLY  
         OPENJSON(Languages)
             WITH (ID INT '$.ID') as B
     WHERE 
         X.ID = 1) AS A

Can anybody tell me whats the mistake I am making? Or do I have to try a different approach for this problem?

Answer 1

Logically, It should work

 SELECT 
         X.ID,
         X.Title,
        (
        select cast(ID as varchar) +',' from OPENJSON(x.Languages)
             WITH (ID INT '$.ID')
             for xml path('')
        ) LanguageID
     FROM 
         ClassTable X where x.id=1