如何在PostgreSQL中查询嵌套JSONB列的特定蛋白相关数据？

针对你存储在JSONB列中的基因表达数据，可通过以下SQL语句实现特定蛋白的pval/FoldChange范围查询：

核心思路 #

利用jsonb_array_elements函数展开Gexp->'data'中的JSON数组，将每个蛋白的属性拆分为独立行，再针对目标属性做过滤。

示例1：查询特定蛋白pval处于指定范围的样本 #

筛选蛋白A0A0B4J2D5中pval < 0.05的样本：

SELECT DISTINCT ge.Sample
FROM gene_expression ge
JOIN jsonb_array_elements(ge.Gexp->'data') AS protein_data
ON protein_data->>'Protein' = 'A0A0B4J2D5'
WHERE (protein_data->>'pval')::numeric < 0.05;

示例2：查询特定蛋白FoldChange处于指定范围的样本 #

筛选蛋白A0FGR8中FoldChange > 2的样本及对应蛋白数据：

SELECT ge.Sample, protein_data::jsonb
FROM gene_expression ge
JOIN jsonb_array_elements(ge.Gexp->'data') AS protein_data
ON protein_data->>'Protein' = 'A0FGR8'
WHERE (protein_data->>'FoldChange')::numeric > 2;

示例3：组合条件查询 #

同时筛选蛋白A0A0B4J2D5的pval在0.01-0.05之间、且FoldChange绝对值大于1的记录：

SELECT ge.Sample, protein_data::jsonb
FROM gene_expression ge
JOIN jsonb_array_elements(ge.Gexp->'data') AS protein_data
ON protein_data->>'Protein' = 'A0A0B4J2D5'
WHERE 
  (protein_data->>'pval')::numeric BETWEEN 0.01 AND 0.05
  AND ABS((protein_data->>'FoldChange')::numeric) > 1;

关键说明 #

• 使用->>提取JSON属性的字符串值，再通过::numeric转换为数值类型，确保数值比较逻辑正确（直接字符串比较会导致错误结果）。
• DISTINCT用于去重，避免同一个样本因多条匹配数据被重复返回；若需保留所有匹配的蛋白数据，可去掉DISTINCT。

内容的提问来源于stack exchange，提问作者RanonKahn