Home / Q&A / How to select distinct elements of an array column in a view
You can translate the question and the replies:

How to select distinct elements of an array column in a view

I need to select distinct values from few array columns in a view. My view consists few Array columns and I cannot group by them. SELECT * from {view} where {parameter} = '03645' | Parameter | Column 2 | Column 3 |Column 4 | | -------- | -------- | -------- | | 03645 | Array wih duplicate elements | Array wih duplicate elements |Array wih duplicate elements |
#array #customfunction ARRAY Distinct
user
23-02-2021 11:22:54 -0500

3 Answers

Hi, In order to get the distinct values of an array field in a view, I would use a similar VQL statement as given below, *SELECT DISTINCT <fieldname> from FLATTEN <viewname> as v (v.<arrayfieldname>);* For more information on using the FLATTEN, you can refer to the [FLATTEN View (Flattening Data Structures)](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/queries_select_statement/from_clause/flatten_view_flattening_data_structures) section of Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
24-02-2021 05:57:03 -0500
Hi, thanks for the response, but my case is different : I need distinct array elements from Columns 2,3,4(<arrayfieldname>) for particular parametr(<fieldname>); for example in Column 2 I have {cat, dog, bird, dog, cat} and I need only distinct - cat, dog, bird. Thank you
user
24-02-2021 06:23:52 -0500
Hi, To obtain distinct values from three different array columns based on a filter condition, I would do the following in Virtual DataPort Administration tool, * Create three FLATTEN views to flatten the three Array columns graphically, as it is possible to select just one array on each ‘flattening’ operation. * Create a [union](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_derived_views/creating_union_views/creating_union_views) view on top of the three flatten views. * Execute the SELECT DISTINCT query over the union view with the filter condition. For more information on creating the FLATTEN views, take a look at the [Creating Flatten Views](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_derived_views/creating_flatten_views/creating_flatten_views#creating-flatten-views) section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
04-03-2021 04:55:15 -0500
You must sign in to add an answer. If you do not have an account, you can register here