RE: Wildcarding json keys in json query

[Patrick FICHE <Patrick.Fiche@xxxxxxxxxxx>]

I guess you could try something like this

 

SELECT * FROM ( SELECT * from json_each(( '{

                                              "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}

                                                  }

                                              }'::json->'ports'

                                        ))

              ) T

WHERE (value::json->>'mean')::float >= 7;

 

From: David Gauthier <davegauthierpg@xxxxxxxxx>
Sent: Tuesday, November 30, 2021 9:40 PM
To: Postgres General <pgsql-general@xxxxxxxxxxxxxx>
Subject: Wildcarding json keys in json query

 

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!