Flatten a JSON array column to distinct result-CodePudding

Home > Blockchain > Flatten a JSON array column to distinct result

Flatten a JSON array column to distinct result

Time：09-13

I have to select from a table with JSON array column

Id  Name   ListCol
-------------------
1   test1  [1,2,3]
2   test2  [4]
3   test3  [1,4]
4   test4  [2]

If I query:

SELECT [ListCol] 
FROM [dbo].[SomeTable]

it returns

[1,2,3]
[4]
[1,4]
[2]

What I expect is

CodePudding user response：

Simply use distinct with openjson

select distinct j.value
from t
cross apply OpenJson(ListCol)j;