By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with the_table (col) as (
values
('{"names": { "jack": { "probability": 0},
"bob": { "probability": 0.5},
"ana": {"probability": 0.2},
"bill": {}
}}'::jsonb)
)
select n.name, coalesce(val ->> 'probability', '0')::numeric
from the_table t
cross join jsonb_each( t.col -> 'names') as n(name, val)
| name | coalesce |
|---|---|
| ana | 0.2 |
| bob | 0.5 |
| bill | 0 |
| jack | 0 |