I have a VARCHAR column with arrays in it (string as a array) in Oracle sql dialect how can I count the distinct values in it?
for example I have the following rows
ID List
1 ["351","364"]
2 ["364","351"]
3 ["364","951"]
4 ["951"]
I expected to count 3.
CodePudding user response：
Assuming you're on a recent version of Oracle, you can use JSON functions to extract the elements of the arrays:
select t.id, j.value
from your_table t
outer apply json_table (t.list, '$[*]' columns (value path '$')) j
| ID | VALUE |
|---|---|
| 1 | 351 |
| 1 | 364 |
| 2 | 364 |
| 2 | 351 |
| 3 | 364 |
| 3 | 951 |
| 4 | 951 |
And then just count the distinct values:
select count(distinct j.value) as c
from your_table t
outer apply json_table (t.list, '$[*]' columns (value path '$')) j
| C |
|---|
| 3 |