CODEWITHSUNDEEP
postgresql
Sigfried
Sigfried

Reputation: 3110

postgres expression to select elements from array

I want to select certain elements from an array column. I know you can do it by position, but I want to filter on content. Here's my data:

     table_name      |       column_names
---------------------+---------------------------------------------------------------
attribute_definition | {attribute_type_concept_id}
cohort_definition    | {definition_type_concept_id,subject_concept_id}
condition_occurrence | {condition_concept_id,condition_source_concept_id,condition_type_concept_id}
death                | {cause_concept_id,cause_source_concept_id,death_impute_concept_id,death_type_concept_id}
device_exposure      | {device_concept_id,device_source_concept_id,device_type_concept_id}
drug_exposure        | {dose_unit_concept_id,drug_concept_id,drug_source_concept_id,drug_type_concept_id,route_concept_id}

What I would like to say is something like:

SELECT table_name,
       array_agg(SELECT colname FROM column_names WHERE colname LIKE '%type%') AS type_cols,
       array_agg(SELECT colname FROM column_names WHERE colname NOT LIKE '%type%') AS other_cols
FROM mytable
GROUP BY table_name

And the result I would like would be:

     table_name      |     type_cols                |     other_cols
----------------------+--------------------------------------------------------------------------------------------------------------
attribute_definition | {attribute_type_concept_id}  | {}                                                                          
cohort_definition    | {definition_type_concept_id} | {subject_concept_id}                                                      
condition_occurrence | {condition_type_concept_id}  | {condition_concept_id,condition_source_concept_id}                         
death                | {death_type_concept_id}      | {cause_concept_id,cause_source_concept_id,death_impute_concept_id}             
device_exposure      | {device_type_concept_id}     | {device_concept_id,device_source_concept_id}                                  
drug_exposure        | {drug_type_concept_id}       | {dose_unit_concept_id,drug_concept_id,drug_source_concept_id,route_concept_id}

So, I want to end up with the same number of rows but different columns. There's gotta be a simple way to do this. Why can't I find it?

Upvotes: 1

Views: 148

Answers (2)

Sigfried
Sigfried

Reputation: 3110

Here is Dan Getz's answer again but in a self-contained statement so it's easily runnable without copying my data.

with grps as
(
  with numlist as
  (
    select '1 - 10' as grp, generate_series(1,10) num
    union
    select '11 - 20', generate_series(11,20) order by 1,2
  )
  select grp, array_agg(num) as nums
  from numlist
  group by 1
)
select grp,
        (select array_agg(evens) from unnest(nums) as evens where evens % 2 = 0) as evens,
        (select array_agg(odds) from unnest(nums) as odds where odds % 2 != 0) as odds
from grps
group by grp, nums;

   grp   |      evens       |       odds
---------+------------------+------------------
 11 - 20 | {12,14,16,18,20} | {11,13,15,17,19}
 1 - 10  | {2,4,6,8,10}     | {1,3,5,7,9}

Upvotes: 1

Dan Getz
Dan Getz

Reputation: 18227

unnest is your friend. As in:

SELECT table_name,
       array(SELECT colname FROM unnest(column_names) AS colname WHERE colname LIKE '%type%') AS type_cols,
       array(SELECT colname FROM unnest(column_names) AS colname WHERE colname NOT LIKE '%type%') AS other_cols
FROM mytable
GROUP BY table_name, column_names

Upvotes: 2

Related Questions