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!
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 |