postgres expression to select elements from array

Question

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?

Dan Getz · Accepted Answer · 2016-12-18 13:06:18Z

2

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

edited Dec 18, 2016 at 13:06

answered Dec 17, 2016 at 13:05

Dan Getz

18.3k2 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sigfried Over a year ago

I'm getting an error: ERROR: column "colname" does not exist LINE 2: array(SELECT colname FROM unnest(column_names) WHERE .... Do the items in the array have a selectable column name?

Dan Getz Over a year ago

Oops, needs to add 'as colname' after the 'unnest'. Fixed in answer

Sigfried Over a year ago

Also need to add column_names to the group by. But that's it. Thanks!

Dan Getz Over a year ago

Yep, fixed in answer. This is what happens when you reply without actually running (as an aside, if the question included a link to an easier format to create the question DB it would help testing - perhaps a link to a pg_dump of the tables)

Sigfried Over a year ago

I just wrote a self-contained statement to demonstrate it. I don't think it will fit in a comment nicely so I'll add it as an answer.

Sigfried · Accepted Answer · 2016-12-19 01:36:10Z

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}

Collectives™ on Stack Overflow

postgres expression to select elements from array

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related