Блог

Когда я пишу запрос postgresql, одна из моих ячеек находится в формате json. Я хочу проанализировать небольшую часть 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}
]
 

Основываясь на «Выбранном»: Верно, я хочу вытащить «Текст»:(Я хочу только этот единственный ответ в своей ячейке). таким образом, в этом примере это не было бы подано: Другое

заранее спасибо

Приведенный ниже запрос должен работать для вас

 select tmp.value1 -> 'text' as TEXT, json_data.key, json_data.VALUE FROM
(select json_array_elements('[{"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}]') as value1) tmp,
 json_each_text(tmp.value1::json) json_data
 where json_data.key = 'selected'
 and json_data.VALUE = 'true'
 

Это должно сработать (используется один оператор select, json_array_elements который намного быстрее и оптимизирован).

 
SELECT value->'text' AS text ,
       value->'selected' AS selected
FROM json_array_elements('[{"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}]'::json)
WHERE (value->>'selected')::boolean IS TRUE;

               text               | selected
---------------------------------- ----------
 "Not Pitched: Other (See Notes)" | true
(1 row)
 

 select v ->> 'text' text_true
from json_array_elements
(
 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}]'
) v
where (v ->> 'selected')::boolean;