Postgresql query, parsing json from column-CodePudding

Here is my Query

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

I am trying to create a column that returns only one value based off a criteria in the json. When I run this I just get a blank query though. This is what the json looks like that I am trying to parse


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

I want to capture the portion that looks like this {"status":"active","text":"Not Pitched: Other (See Notes)","id":2,"color":"DCEBD8","selected":true} and want my cell to just show

Not Pitched: Other (See Notes) based on "selected":true".

I have not been able to get this to work

Any help would be awesome! Thank you!

CodePudding user response：

First you should use the jsonb type instead of json, see the documentation :

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

If you use jsonb, then this simple query shoud provide what you expect :

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

CodePudding user response：

outcome::json ->> 'selected' looks for the key selected at the top level. If the outcome was {"text":"Not Pitched: Failed Credit","selected":false} that would be fine.

But it isn't, it's an array. You need to search the JSON array. First, turn the elements of the array into rows with 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...

Then use that as a subquery to search it.

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

Note: Consider changing it to jsonb; it will be faster and also prevent invalid JSON.