我想从数组列中选择某些元素。我知道你可以按职位来做,但我想过滤内容。这是我的数据:
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/