[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Wildcarding json keys in json query
From:       David Gauthier <davegauthierpg () gmail ! com>
Date:       2021-11-30 20:40:10
Message-ID: CAMBRECCKusTv=uNUp6JoBjvHog1vutf3gqO2gFJ__xgamCBFrw () mail ! gmail ! com
[Download RAW message or body]

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!

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">PG \
11.5 on linux</div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">Let&#39;s say I store a jsonb  in a column \
called test_results that looks like this...</div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">{</div><div class="gmail_default" \
style="font-family:monospace,monospace">ports : {</div><div class="gmail_default" \
style="font-family:monospace,monospace">      port_abc:{min: 5, max: 7, mean: \
6},</div><div class="gmail_default" style="font-family:monospace,monospace">      \
port_def:{min: 5, max: 9, mean: 7},<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">      port_ghi:{min: 6, max: 10, mean: \
8}<br></div><div class="gmail_default" style="font-family:monospace,monospace">      \
}</div><div class="gmail_default" style="font-family:monospace,monospace">}</div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">And I want to to get \
all the port names where the mean is &gt;= 7.</div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">This works...</div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">select 1 from mytbl where \
cast(test_results#&gt;&#39;{ports,<font \
color="#ff0000"><b>port_abc</b></font>,mean}&#39; as float) &gt;= 7 ;<br></div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">But I want <b><i>all \
</i></b>the ports that have mean &gt;= 7.</div><div class="gmail_default" \
style="font-family:monospace,monospace">Something akin to...</div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace"><div \
class="gmail_default">select 1 from mytbl where \
cast(test_results#&gt;&#39;{ports,<font color="#ff0000"><b>*</b></font>,mean}&#39; as \
float) &gt;= 7 ;<br></div><div class="gmail_default"></div></div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">But the  &quot;*&quot; \
doesn&#39;t work :-(<br><br>Any ideas ?<br>Thanks!</div></div>



[prev in list] [next in list] [prev in thread] [next in thread]