Postgres Pro
Downloads
Home   >   mailing lists

Thread: Wildcarding json keys in json query

Wildcarding json keys in json query

From
David Gauthier
Date:
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!

Re: Wildcarding json keys in json query

From
"David G. Johnston"
Date:
On Tue, Nov 30, 2021 at 1:40 PM David Gauthier <davegauthierpg@gmail.com> wrote:
{
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}
    }
}

select 1 from mytbl where cast(test_results#>'{ports,*,mean}' as float) >= 7 ;

But the "*" doesn't work :-(

Any ideas ?


If you have the option to not use data values in json field identifiers (i.e., object keys) you will avoid fighting against the system's underlying design choices.  i.e., IMO, ports should contain an array, not an object.

I was under the impression that SQL/JSON Path (jsonpath) functions/operators solve some of these kinds of problems so try using that instead of the "fixed structure" access-based functions and operators.

David J.

RE: Wildcarding json keys in json query

From
Patrick FICHE
Date:

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@gmail.com>
Sent: Tuesday, November 30, 2021 9:40 PM
To: Postgres General <pgsql-general@postgresql.org>
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!