CODEWITHSUNDEEP
jsonsql-servert-sql
sprocket12
sprocket12

Reputation: 5488

How to merge results of OPENJSON into single list and remove duplicates?

I have data like:

enter image description here

I would like to use OPENJSON to make these ids into a single list:

1001
1002
1003
5
6

And also to remove any duplicates if possible. What is the correct way to achieve this?

Upvotes: 0

Views: 194

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

You can use OPENJSON in concert with a CROSS APPLY

Example

Declare @YourTable Table ([Users] varchar(50))  
Insert Into @YourTable Values 
 ('[1001,1002,1003]')
,('[5]')
,('[6]')
 
Select Distinct B.Value
 From  @YourTable A
 Cross Apply ( Select * from OpenJSON([Users]) ) B

Returns

Value
1001
1002
1003
5
6

Upvotes: 1

Related Questions