Parsing Json postgresql

Solution for Parsing Json postgresql
is Given Below:

When I write a postgresql query one of my cells is in json format. I want to parse a small portion of the JSON based on a criteria. For example this is the json format

[
{"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}
]

Based on the “Selected”: True I want to pull the “Text”:(I want just this lone answer in my cell). so in this example it would be Not Pitched: Other

thanks in advance

Below Query should work for you

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'

This should work (uses single select statement, json_array_elements which is much faster and optimized).


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;