Postgresql查询，从列解析json

Question

这是我的查询

select outcome::json -> 'text' as outcome
from "Deal"
where (outcome::json ->> 'selected')::boolean;

我试图创建一个列，只返回一个基于json中的标准值。当我运行这个，我只是得到一个空白的查询虽然。这就是我要解析的json

[{"status":"active","text":"Not Pitched: Failed Credit","id":11,"color":"DCEBD8","selected":false},{"status":"active","text":"Not Pitched: No Show","id":3,"color":"DCEBD8","selected":false},{"status":"active","text":"Not Pitched: No Tax Incentive","id":7,"color":"DCEBD8","selected":false},{"status":"active","text":"Not Pitched: Shaded","id":8,"color":"DCEBD8","selected":false},{"status":"active","text":"Not Pitched: Other (See Notes)","id":2,"color":"DCEBD8","selected":true},{"status":"active","text":"Not Pitched: Renter","id":9,"color":"DCEBD8","selected":false},{"status":"active","text":"Pitched: Not Interested","id":5,"color":"DCEBD8","selected":false},{"status":"active","text":"Pitched: Pending","id":10,"color":"DCEBD8","selected":false},{"status":"active","text":"Pitched: Closed","id":1,"color":"DCEBD8","selected":false},{"status":"active","text":"Pitched: Missed","id":6,"color":"DCEBD8","selected":false},{"status":"active","text":"Pitched: Manually Set by Fluent","id":12,"color":"DCEBD8","selected":false},{"status":"deleted","text":"Not Pitched: Other (See Notes)","id":4,"color":"DCEBD8","selected":false}]

我想捕获看起来像这样的部分{"status";active";text";Not pitch: Other(参见注释)";id"; 2; "color";DCEBD8"; "selected":true}，并希望我的单元格只显示

未音调:其他(见注释)基于"selected"; true"

我没能让这个工作

任何帮助都太棒了!谢谢你！

Answer 1

首先，您应该使用jsonb类型而不是json，请参阅文档:

一般来说，大多数应用程序应该倾向于将JSON数据存储为Jsonb，除非有非常特殊的需求，比如遗留关于对象键排序的假设。

如果您使用jsonb，那么这个简单的查询应该提供您所期望的:

SELECT jsonb_path_query(outcome :: jsonb, '$[*] ? (@.selected == true).text') AS outcome
FROM Deal

Answer 2

outcome::json ->> 'selected'查找顶级键selected。如果outcome是{"text":"Not Pitched: Failed Credit","selected":false}，那就可以了。

但它不是，它是一个数组。您需要搜索JSON数组。首先，使用jsonb_array_elements将数组中的元素转换为行。

select jsonb_array_elements(outcome) from deal;
jsonb_array_elements                                 >
--------------------------------------------------------------------------------------------------->
{"id": 11, "text": "Not Pitched: Failed Credit", "color": "DCEBD8", "status": "active", "selected">
{"id": 3, "text": "Not Pitched: No Show", "color": "DCEBD8", "status": "active", "selected": false}
{"id": 7, "text": "Not Pitched: No Tax Incentive", "color": "DCEBD8", "status": "active", "selecte>
...etc...

然后使用它作为子查询来搜索它。

select outcome
from (
select jsonb_array_elements(outcome::jsonb) as outcome
from deal
) as outcomes
where (outcome->'selected')::boolean;

注意:考虑改成jsonb;它将更快，还可以防止无效的JSON。