我想从数组列中选择某些元素。我知道你可以按职位来做,但我想过滤内容。这是我的数据:

     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}

我想说的是:
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

我想要的结果是:
     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}

所以,我想以相同的行数结束,但列数不同。一定有个简单的方法。为什么我找不到?

最佳答案

unnest是你的朋友。如所示:

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

关于postgresql - postgres表达式以从数组中选择元素,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/41198736/

10-10 13:53
查看更多