PostgreSQL查询JSON数组中State为'attached'的行求助

你的查询失败原因如下：

• 第一个查询SELECT attachments FROM aws_ec2_ebs_volumes WHERE 'State' = 'attached';是常量字符串比较，永远为false，所以不会返回任何行。
• 第二个查询SELECT attachments FROM aws_ec2_ebs_volumes WHERE attachments ->> 'State' = 'attached';错误地将JSON数组当作单个JSON对象处理，尝试直接提取State字段，但数组本身没有这个字段，因此条件不成立。

针对JSON数组类型的attachments字段，以下是几种正确的查询方法：

方法1：拆分数组并筛选（返回所有匹配的附件行） #

如果需要获取每个符合条件的附件记录（一个卷可能对应多行结果），可以用json_array_elements（字段类型为json时）或jsonb_array_elements（字段类型为jsonb时）拆分数组：

SELECT vol.attachments, att.*
FROM aws_ec2_ebs_volumes vol
JOIN json_array_elements(vol.attachments) att 
  ON att ->> 'State' = 'attached';

方法2：用EXISTS子查询（返回唯一的卷行） #

如果只需要返回包含至少一个State为attached的附件的卷，避免重复行，用EXISTS子查询更合适：

SELECT attachments
FROM aws_ec2_ebs_volumes
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(attachments) att
    WHERE att ->> 'State' = 'attached'
);

方法3：用JSONB包含操作符（简洁高效，仅适用于jsonb类型） #

如果attachments字段是jsonb类型，可以直接用@>操作符检查数组是否包含指定结构的对象：

SELECT attachments
FROM aws_ec2_ebs_volumes
WHERE attachments @> '[{"State": "attached"}]'::jsonb;

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