Thread: JSON query help

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!

Got a bit close now:

cgrates=# select
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
as balance from cdrs where cost > 0 limit 1;
-[ RECORD 1 ]-----------------------------------
BalanceUUID | "154419f2-45e0-4629-a203-06034ccb493f"

so I can AND this with cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries'
@>'[{"ID":"B_MONETARY_POSTPAID"}]' limit 10;

now I just need to use the first select which returns a SET to loop
through BalanceSummaries to match on BalanceUUID = UUID

Any pointers?

I think I'm pretty close:

SELECT
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost
FROM cdrs
WHERE cost > 0
AND cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]'
AND cost_details->'AccountSummary'->'BalanceSummaries' @>
jsonb_build_object('UUID'::text,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')::jsonb
LIMIT 1;

ERROR:  function jsonb_build_object(text, jsonb) does not exist
LINE 1: ..._details->'AccountSummary'->'BalanceSummaries' @> jsonb_buil...
                                                             ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

Gavin Henry <gavin.henry@gmail.com> writes:
> ERROR:  function jsonb_build_object(text, jsonb) does not exist

Hm ... if the server knows jsonb but not jsonb_build_object(),
it must be 9.4.x, which is pretty old.  9.4.x will be EOL in
November, if memory serves.  You should think about upgrading.

            regards, tom lane

Hi Tom,

Thanks. I think it's because I'm casting wrongly or doing this wrong.

Hi Tom,

This is 9.6 with BDR in. Is my way to tackle this correct? To restate,
there is an event in the top level json with a list of things with a
BalanceUUID in. Then a list of your balances. I want to pull out
records that have used one of those balances, but there could be one
or more in there, hence the jsonb_object_keys

Here's some other errors trying this.

HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
SELECT cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost from cdrs WHERE cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]' AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
jsonb_build_object('UUID',
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')
limit 1;
ERROR:  function jsonb_build_object(unknown, jsonb) does not exist
LINE 1: ..._details->'AccountSummary'->'BalanceSummaries' @> jsonb_buil...
                                                             ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
SELECT cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost from cdrs WHERE cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]' AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
json_build_object('UUID',
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')
limit 1;
ERROR:  operator does not exist: jsonb @> json
LINE 1: ...ost_details->'AccountSummary'->'BalanceSummaries' @> json_bu...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
SELECT cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost from cdrs WHERE cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]' AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
jsonb_build_object('UUID',
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')
limit 1;
ERROR:  function jsonb_build_object(unknown, jsonb) does not exist
LINE 1: ..._details->'AccountSummary'->'BalanceSummaries' @> jsonb_buil...


How can I check if that function does exist?

Thanks.

Gavin Henry <gavin.henry@gmail.com> writes:
> This is 9.6 with BDR in.

Um ... I bet not.  Maybe you're confusing the version of psql with
that of the server?  Because jsonb_build_object() is definitely
there in 9.6.

> ERROR:  operator does not exist: jsonb @> json

This one you just need a cast for --- the server won't automatically
convert json to jsonb.  (I don't remember why we didn't make that
cast implicit, but probably there was a good reason.)

> How can I check if that function does exist?

On a 9.6 server, you should get

=# \df jsonb_build_object
                                 List of functions
   Schema   |        Name        | Result data type | Argument data types |  Type
------------+--------------------+------------------+---------------------+--------
 pg_catalog | jsonb_build_object | jsonb            |                     | normal
 pg_catalog | jsonb_build_object | jsonb            | VARIADIC "any"      | normal
(2 rows)

If, as I suspect, it's really 9.4, you could substitute
    json_build_object(...)::jsonb

            regards, tom lane

On Wed, 28 Aug 2019 at 21:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Gavin Henry <gavin.henry@gmail.com> writes:
> > This is 9.6 with BDR in.
>
> Um ... I bet not.  Maybe you're confusing the version of psql with
> that of the server?  Because jsonb_build_object() is definitely
> there in 9.6.

Ah, yes. My apologies. 9.4.15 with BDR the server shows. I'm still
investigating what to do to get on to v11 (probably v12) and am
looking at rolling out Pgpool II with each client. What are others
doing here? Hot Standby? Currently reading
https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
(any other advice?).

> > ERROR:  operator does not exist: jsonb @> json
>
> This one you just need a cast for --- the server won't automatically
> convert json to jsonb.  (I don't remember why we didn't make that
> cast implicit, but probably there was a good reason.)

Thanks, will do.

> > How can I check if that function does exist?
>
> On a 9.6 server, you should get
>
> =# \df jsonb_build_object
>                                  List of functions
>    Schema   |        Name        | Result data type | Argument data types |  Type
> ------------+--------------------+------------------+---------------------+--------
>  pg_catalog | jsonb_build_object | jsonb            |                     | normal
>  pg_catalog | jsonb_build_object | jsonb            | VARIADIC "any"      | normal
> (2 rows)
>
> If, as I suspect, it's really 9.4, you could substitute
>         json_build_object(...)::jsonb
>
>                         regards, tom lane


Yep, emtpy:

\df jsonb_build_object
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

Will use your suggestion and report back.

Thanks, that worked! Now I need to go back to basics and SETS:

SELECT
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost
FROM cdrs
WHERE cost > 0
AND cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]'
AND cost_details->'AccountSummary'->'BalanceSummaries' @>
json_build_object('UUID',
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')::jsonb
limit 1;
ERROR:  argument of AND must not return a set
LINE 1: ...ummaries' @> '[{"ID":"B_MONETARY_POSTPAID"}]' AND cost_detai...