這是我的查詢
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 Pitched: Other (See Notes)","id":2,"color":"DCEBD8","selected ":true} 并希望我的單元格只顯示
Not Pitched：其他（見注釋）基于“selected”：true”。
我一直無法讓這個作業
任何幫助都是極好的！謝謝！
uj5u.com熱心網友回復：
首先，您應該使用 jsonb 型別而不是 json，請參閱檔案：
一般來說，大多數應用程式應該更喜歡將 JSON 資料存盤為 jsonb，除非有非常特殊的需求，例如關于物件鍵排序的遺留假設。
如果您使用 jsonb，那么這個簡單的查詢應該提供您所期望的：
SELECT jsonb_path_query(outcome :: jsonb, '$[*] ? (@.selected == true).text') AS outcome
FROM Deal
uj5u.com熱心網友回復：
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。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/337124.html
標籤：json PostgreSQL
下一篇：postgresql動態查詢