Wildcarding json keys in json query

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: Raw Message | Whole Thread | 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!

Responses

Browse pgsql-general by date

  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?