Get all users from Json string

Question

I have 2 table like above

CREATE Table #Table1 (GroupID varchar(5), UserList varchar(250));
CREATE Table #Table2 (AppLevel int, Condition varchar(100));

insert into #Table1 values ('G1','[{"User":"1"}]');
insert into #Table1 values ('G2','[{"User":"2"},{"User":"3"},{"User":"4"},{"User":"5"}]');
insert into #Table1 values ('G3-1','[{"User":"6"},{"User":"7"},{"User":"8"},{"User":"9"}]');
insert into #Table1 values ('G3-2','[{"User":"13"}]');
insert into #Table1 values ('G3-3','[{"User":"14"},{"User":"15"},{"User":"16"},{"User":"17"}]');

insert into #Table2 values (1, 'G1');
insert into #Table2 values (2, 'G2');
insert into #Table2 values (3, '(G3-1 and G3-2) or G3-3 ');

If I pass condition as ‘G2’ from Table2 It has to return result table as like below

If I pass condition as ‘(G3-1 and G3-2) or G3-3’ from Table2 It has to return result table as like below

Thanks in Advance
Kasim

Answer 1

Hi @kasim mohamed ,

Please refer below and check whether it is helpful.

declare @condition varchar(100)  
declare @AppLevel int=3   --define here 1,2,3  
  
select @condition= Condition  
from #Table2  
where AppLevel=@AppLevel  
  
if @condition='G2'   
SELECT GroupID,b.value  
FROM #Table1 a  
outer APPLY  OPENJSON (UserList) WITH (value NVARCHAR(100) '$.User') b  
where GroupID=@condition  
else if @condition='(G3-1 and G3-2) or G3-3'   
SELECT GroupID,b.value  
FROM #Table1 a  
OUTER APPLY  OPENJSON (UserList) WITH (value NVARCHAR(100) '$.User') b  
where GroupID ='G3-1' or GroupID='G3-2' or GroupID='G3-3'  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Thank you MelissaMa-msft