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 |
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)]( 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
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]( 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]( 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