Search Postgresql Archives

Wildcarding json keys in json query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux