PostgreSQL如何提取JSON数组中selected为true的text字段值

错误原因 #

你原查询返回空的核心原因是：outcome列存储的是JSON数组，而非单个JSON对象，直接对数组执行outcome::json ->> 'selected'无法读取到数组内部对象的selected字段，where条件永远不成立。

正确查询写法 #

需要先将JSON数组展开为单个对象再做筛选，如果你使用的是json类型，写法如下：

SELECT elem ->> 'text' AS outcome
FROM "Deal"
CROSS JOIN LATERAL json_array_elements(outcome::json) AS elem
WHERE (elem ->> 'selected')::boolean;

如果你已经将outcome列设为性能更好的jsonb类型，将json_array_elements替换为jsonb_array_elements即可：

SELECT elem ->> 'text' AS outcome
FROM "Deal"
CROSS JOIN LATERAL jsonb_array_elements(outcome::jsonb) AS elem
WHERE (elem ->> 'selected')::boolean;

逻辑说明 #

1. json_array_elements/jsonb_array_elements函数会把JSON数组拆分为多行，每行对应数组中的一个对象
2. LATERAL关联保证每行表数据和自己展开的JSON对象行对应
3. 筛选出展开后selected为true的对象，读取其text字段返回即可，你的示例场景下会准确返回Not Pitched: Other (See Notes)

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