I have this table:
| arrs |
|:----|
| {10000,10000,10000,10000} |
| {100,200,300} |
| {400,500,600} |
| {100,200,600} |
How get this result?
| uniques |
|:-------|
| 10000 |
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |
Or this result?
| arrUniques |
|:----|
| {10000,100,200,300,400,500,600} |
See -> fiddle
You should be able to use UNNEST() along with DISTINCT here:
SELECT DISTINCT UNNEST(arrs) AS uniques
FROM yourTable;
-
You can use
Distinct unnestto display unique results as per your first scenario :SELECT DISTINCT unnest(arr) AS uniques FROM "test"; -
For your second scenario displaying result in single array you can add
array_agg:SELECT array_agg(DISTINCT unnest(arr)) AS arrUniques FROM "test";
New contributor
Arf_code is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Strictly speaking it’s better to place the unnest into the FROM part, as putting it into the SELECT has some weird effects in some cases.
SELECT DISTINCT
a.value AS uniques
FROM yourTable t
CROSS JOIN LATERAL unnest(t.arrs) AS a(value);
0