JSON query help
От | Gavin Henry |
---|---|
Тема | JSON query help |
Дата | |
Msg-id | CAA8_NKAAQ6gC-5pb9sUOY1yr_On2B7qxsZnFzq8ShXPWvescyg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: JSON query help
|
Список | 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!
В списке pgsql-novice по дате отправления: