| From: | David Gauthier <davegauthierpg(at)gmail(dot)com> |
|---|---|
| To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Wildcarding json keys in json query |
| Date: | 2021-11-30 20:40:10 |
| Message-ID: | CAMBRECCKusTv=uNUp6JoBjvHog1vutf3gqO2gFJ__xgamCBFrw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
PG 11.5 on linux
Let's say I store a jsonb in a column called test_results that looks like
this...
{
ports : {
port_abc:{min: 5, max: 7, mean: 6},
port_def:{min: 5, max: 9, mean: 7},
port_ghi:{min: 6, max: 10, mean: 8}
}
}
And I want to to get all the port names where the mean is >= 7.
This works...
select 1 from mytbl where cast(test_results#>'{ports,*port_abc*,mean}' as
float) >= 7 ;
But I want *all *the ports that have mean >= 7.
Something akin to...
select 1 from mytbl where cast(test_results#>'{ports,***,mean}' as float)
>= 7 ;
But the "*" doesn't work :-(
Any ideas ?
Thanks!
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Gustafsson | 2021-11-30 20:53:16 | Re: Issues cross-compiling libpq 14.x to MacOS armv8 |
| Previous Message | Wicher | 2021-11-30 20:39:09 | Re: How to reveal the codes of functions properly? |