JSON query help

From: Gavin Henry <gavin(dot)henry(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: JSON query help
Date: 2019-08-27 15:30:46
Message-ID: CAA8_NKAAQ6gC-5pb9sUOY1yr_On2B7qxsZnFzq8ShXPWvescyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi all,

I have a jsonb like this (trimmed for this example) in a column on Pg
9.6 with (using BDR fyi):

{
...
"Accounting":{
"6a7a92c":{
"Units":0.2832,
"RatingID":"",
"AccountID":"example:23418",
"BalanceUUID":"154419f2-45e0-4629-a203-06034ccb493f",
"ExtraChargeID":""
}
},
"AccountSummary":{
"ID":"23418",
"Tenant":"example",
"Disabled":false,
"AllowNegative":false,
"BalanceSummaries":[
{
"ID":"B_MONETARY_POSTPAID",
"Type":"*monetary",
"UUID":"154419f2-45e0-4629-a203-06034ccb493f",
"Value":48.8672,
"Disabled":false
},
{
"ID":"B_UK_MOBILE_DATA_3000",
"Type":"*data",
"UUID":"08a05723-5849-41b9-b6a9-8ee362539280",
"Value":3188719616,
"Disabled":false
},
{
"ID":"B_UK_SMS_250",
"Type":"*sms",
"UUID":"06a87f20-3774-4eeb-826e-a79c5f175fd3",
"Value":250,
"Disabled":false
},
{
"ID":"B_UK_MOBILE_UK_LANDLINE_250",
"Type":"*voice",
"UUID":"4ad16621-6e22-4e35-958e-5e1ff93ad7b7",
"Value":14934000000000,
"Disabled":false
}
]
}
}

Now I'm only interested in rows that have an ID of B_MONETARY_POSTPAID
in BalanceSummaries which is easy:

SELECT * FROM cdrs WHERE cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]' limit 10;

but I'd like to go further (or replace) and add new where clause that
only returns rows that have a match in:

AND cost_details->'AccountSummary'->Accounting->"BalanceUUID =
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}->"UUID"]'

so only return cdrs that have used that balance.

Make sense? I'm stuck on the ->UUID value. What operator should I be
reading about here:

https://www.postgresql.org/docs/9.6/datatype-json.html
https://www.postgresql.org/docs/9.6/functions-json.html

Thanks!

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Gavin Henry 2019-08-27 21:26:37 Re: JSON query help
Previous Message Simon Riggs 2019-08-22 17:08:49 Re: Default logging (log_statement) versus pgaudit